April 15, 2003 at 7:03 am
When do you use a sequential number as the primary key to a table versus using the unique combination of fields to be the primary key?
For instance, if I have to use 3 fields to uniquely identify a record in a table which becomes the foriegn key to another table, then those 3 fields plus another field becomes the unique identifier of the child table, why use a sequential number for the primary keys of these tables when I have to make the combination of the three and four fields to uniquely identify a record in those same tables?
Which way is faster for queries?
Which way is easier for the developers?
Etc., etc.???
Thanks,
Thomas LeBlanc
Thomas LeBlanc, MVP Data Platform Consultant
April 15, 2003 at 7:57 am
Well, it depends on other variables - the size of the fields, data types, etc, and the type of transactions.
A sequential number should not be the primary key if none, or a small percentage, of the queries use it in their criteria. What will they use in the majority of their queries? What kind of information will they try to retrieve from the table?
Keep key database operations and transactions in mind as you construct the indexes. Build indexes that the query optimizer will use to service your more crucial transactions. Create an index on a set of columns, if you frequently will join the two tables on those columns - than an index will speed up the join.
Generally speaking, clustered indexes are best for range selections and ordered queries. Clustered indexes are appropriate for queries with high densities (those with many duplicate values). Nonclustered indexes are better for singleton selects and individual row lookups.
I hope this helps.
Patrick
Quand on parle du loup, on en voit la queue
April 15, 2003 at 12:40 pm
Please do not crosspost. See related topic http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=11375&FORUM_ID=9&CAT_ID=1&Topic_Title=Numeric+Primary+Key+vs%2E+Combination+Primary+Key&Forum_Title=General
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply