January 5, 2011 at 3:51 pm
I have a table that contain 65 columns. Of those columns 12 uniquely identify each record. Should I make those 12 columns a composite primary key? Should I add an identity column as primary key (surrogate key) and then build a unique constraint over the 12 columns?
January 5, 2011 at 4:06 pm
Another option:
Extract those 12 columns into a separate table with an identity column and replace the 12 columns in your original table with the id of the new table.
January 5, 2011 at 4:24 pm
I would not make those 12 a clustered index. These 12 columns will be in every single nonclustered index as well, so they will all be wide.
I would lean towards an identity column, make that the clustered key, and add a unique index on the 12 as a separate item.
January 5, 2011 at 4:26 pm
Thank you very much for both of your insights to this problem. I like the idea of the identity and then the unique index over the 12 columns. thank you again.
January 6, 2011 at 7:36 am
Just remember this, if you put all 11 columns into the clustered index, they are stored in every non-clustered index too. Also, if they're the primary key, they're in every table that references this one through a foreign key. I'm not saying you can't using multiple columns for the PK or the cluster, but you need to think about the implications. I'd say, in this case, it sounds like it's not the best choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2011 at 7:41 am
You are recommending against the use of all 12 columns as a primary key? Just want to make sure that I understood your reply.
January 6, 2011 at 8:03 am
Yep, I'm recommending against it. I'm just using a lot of hedge words because you can make it work, but you probably shouldn't.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2011 at 8:04 am
Thank you for your assistance.
January 7, 2011 at 12:42 pm
Brandon Carl Goodman (1/6/2011)
You are recommending against the use of all 12 columns as a primary key? Just want to make sure that I understood your reply.
Technically speaking, you could still designate those 12 columns are the primary key, but opt not to cluster it, and then add a unique integer as a clustered surrogate key. It's the clustered index, not the primary key, that gets used internally as the table's rowid in non-clustered indexes.
However, when data modeling I like to designate the key actually used for foreign key relationships as the primary key, which is in keeping with the term "primary".
So, I would suggest a unique non-clustered index on those 12 columns and then something else, typically an indentity column, as the primary key.
Clustered Index Design Guidelines
http://msdn.microsoft.com/en-us/library/ms190639.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2011 at 1:04 pm
Eric has summarized nicely what I would do.
One note, be sure adding the identity will not break other code.
January 9, 2011 at 11:25 am
Thank you to everybody.
January 9, 2011 at 12:00 pm
just a subtle remark.
Make it a unique constraint ! That is implemented with a unique non-clustered index by sqlserver, but will result in everybody knowing it is a constraint and not a performance / usability created index.
Some modeling tools also interpret that in the visualisation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply