January 22, 2013 at 11:10 pm
In my test enviroment i found that out of 269 tables 99 tables does not have primary keys and 4 tables having 2 primary keys . can u give any suggestions on it. i have to review the structure .
January 22, 2013 at 11:20 pm
m.rajesh.uk (1/22/2013)
In my test enviroment i found that out of 269 tables 99 tables does not have primary keys and 4 tables having 2 primary keys . can u give any suggestions on it. i have to review the structure .
No primary keys on a table can represent a real problem. You need to identify what's unique on those records and add a primary key to provide a CLUSTERED (or non-CLUSTERED) INDEX that will aid in searching.
The tables that you say have 2 primary keys don't really have 2. They have one composite primary key which establishes uniqueness of the row (usually) based on the 2 pieces of information together. It is possible for a primary key to be non-unique (must be specified when you create the key).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 11:26 pm
Thanks for your Valuable suggestion.
January 23, 2013 at 3:29 am
m.rajesh.uk (1/22/2013)
can u give any suggestions on it. i have to review the structure .
Can you broef what kind of suggestion you are looking here ? primary is recommended in database/table design to represent the record's uniqueness but its not necessary to have that .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 23, 2013 at 7:55 am
dwain.c (1/22/2013)
m.rajesh.uk (1/22/2013)
In my test enviroment i found that out of 269 tables 99 tables does not have primary keys and 4 tables having 2 primary keys . can u give any suggestions on it. i have to review the structure .No primary keys on a table can represent a real problem. You need to identify what's unique on those records and add a primary key to provide a CLUSTERED (or non-CLUSTERED) INDEX that will aid in searching.
The tables that you say have 2 primary keys don't really have 2. They have one composite primary key which establishes uniqueness of the row (usually) based on the 2 pieces of information together. It is possible for a primary key to be non-unique (must be specified when you create the key).
A Primary Key is not defined to "aid in searching" but to establish Referential Integrity - furthermore, for that reason a Primary Key has to be unique.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 23, 2013 at 6:43 pm
PaulB-TheOneAndOnly (1/23/2013)
dwain.c (1/22/2013)
m.rajesh.uk (1/22/2013)
In my test enviroment i found that out of 269 tables 99 tables does not have primary keys and 4 tables having 2 primary keys . can u give any suggestions on it. i have to review the structure .No primary keys on a table can represent a real problem. You need to identify what's unique on those records and add a primary key to provide a CLUSTERED (or non-CLUSTERED) INDEX that will aid in searching.
The tables that you say have 2 primary keys don't really have 2. They have one composite primary key which establishes uniqueness of the row (usually) based on the 2 pieces of information together. It is possible for a primary key to be non-unique (must be specified when you create the key).
A Primary Key is not defined to "aid in searching" but to establish Referential Integrity - furthermore, for that reason a Primary Key has to be unique.
School's in!
Thanks Paul. Of course you're technically correct on referential integrity.
And as to primary keys being unique, I must have been confusing this with INDEX.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply