Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-06-26 : 09:56:48
|
| I have a table that currently has 5 columns to make each record unique. Because of the constant changing and constant comparing, I am unable to stick an integer identity column to it or anything of that sort. The project manager here has put a primary key on all 5 columns and I'm left wondering if that is a good idea. My initial reaction is no bad because I run joins off this table and am going to have to use 5 columns in the join statement. But other then that... Can anyone see any possible issues and things to watch for on this one with this one?-----------------------Take my advice, I dare ya |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-26 : 10:32:41
|
| There is nothing inherantly wrong with a 5-way composite primary key. How are you indexing the table? If you pk is a clustered index, then you non-clustered indexes are going to have to carry all 5 columns on their leaf nodes to do the bookmark lookup. So that may present a space issue. You'll want to make sure you take a look at selectivity so that on your pk index you have the most selective column first.<O> |
 |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-06-26 : 13:13:14
|
| It's my understanding that the data you select for your "primary key" should be data that essentially does not change. This is because every time the data changes, SQL Server has to do work to relocate the leaf data (for this clustered index). In the case described here, I am wondering if one or more of the columns has its data frequently changing. If the data is frequently changing, maybe a unique non-clustered index on the column composite would provide better performance characteristics than defining a "primary key".Other views, comments??solart |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-26 : 15:12:06
|
| Fortunately in this case this data will never be updated, only deleted or inserted.thnx for your input solart-----------------------Take my advice, I dare ya |
|
|
|
|
|
|
Multiple columns as primary key