Bill Talada (4/30/2014)
The number one rule of a primary key is that it must be "meaningless" yet unique. To be meaningless it must not be one of your four columns that are under user control. Your four columns can and should be an Alternate Key (unique constraint/index).I've gotten away from Identities as primary keys and I feel liberated. Identities are non-ANSI and prevent batch insertion. Personally, I prefer uniqueidentifiers for the PK.
All non-clustered indexes automatically include the primary key so if you want skinny indexes use a single column for your primary key.
Why a guid over an identity? They take up a lot more storage and are a complete PITA to debug. Non-ANSI honestly doesn't really matter because database agnostic is a farce.
I am not sure how switching from one type of generated value to a different (and more painful) one makes you feel liberated.
What do you mean that an identity prevents batch insertion? That is simply not true.
Also, if you are using a super big column like a guid for a primary key you need to make sure your primary key is NOT clustered. You need to find something else to use as your clustered index.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/