April 16, 2003 at 6:50 am
I am posting this again, in General, and not in Statagies because I am hoping for more insight. Sorry for the double post, repost:
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 17, 2003 at 5:53 am
Speed for joins either way in most cases are roughly the same due to the way 32bit architecture handles data comparison.
Developers may find either easier but in general it is easier to deal with the data going in not coming out but that isn't bad either.
Example:
Table with 3 columns as PK when data is inserted they developer already knows the key and an SP can be built to apply to the other tables based on data.
But with a Numericc primary key such as an INT you have to either lookup the entry (not best way) or use @@IDENTITY or one of the others to get the new value inserted to provide to the related tables.
Another factor is how many unique values there will be, you have to consider if the Numeric value supports a values to cover all the records.
Finally is index size. For instance if the PK is based on 3 columns and is clustered then all non-clustered indexes will be that width plus the width of the column(s) involved for that index. And your related tables will of course hve that impact as well.
Note: With composite indexes such as the 3 columns one presents make sure your most unique to least unique items are the column order of the index as the stats are based on the fist column in the index.
April 17, 2003 at 6:15 am
I agree with all that. Using @@identity (or scope_identity()) isn't that much to manage and if you work smartly, can all be done on the server instead of multiple round trips.
Andy
April 17, 2003 at 12:00 pm
Thanks for y'all's comments.
I have always used "codes" for lookup type tables, and multi column primary keys for transaction tables because most applications I have programmed do not have a significant amount of data, other than a Data Mart I did(updated once a day, summaries once a month).
We have some developers that insist on using just Numeric(Identity) columns for primary keys.
My plan is to stick with "Codes" for lookup tables, and change to numeric(identity) columns for transaction type tables.
Thanks again,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply