What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.Originally posted by Chuckt
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
pettrer: