Still amazes me - yet it doesn't surprise me anymore - how this "surrogate Vs natural key debate" goes on and on.
Please let me start by stating my position on the issue which is: "Use Natural keys whenever possible, use Surrogate keys whenever necessary(*)"
(*) Like in FACT-DIM relationships in a dimensional model.
Having said that I think I've figured out why this "debate" gets particularly intense in the SQL Server universe - it has to be with clustered indexes on identity columns, please allow me to explain further.
Let me quote Microsoft recommendations on "Clustered Index Design Guidelines" where it reads...
With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:
- Can be used for frequently used queries.
- Provide a high degree of uniqueness.
- Can be used in range queries.
. . .
Generally, you should define the clustered index key with as few columns as possible. Consider columns that have one or more of the following attributes:
- Are unique or contain many distinct values
- Are accessed sequentially
- Used frequently to sort the data retrieved from a table
- Defined as IDENTITY because the column is guaranteed to be unique within the table
I personally find these guidelines sound but I also think some DBA construct them as "you must have a clustered index on an identity column" missing a couple of key words in Microsoft's documentation like "with few exceptions" and "columns that have one or more of the following attributes".
My interpretation of the guidelines goes like: a clustered index is helpfull if chosen column is used in certain ways and is already defined in certain ways - which may (or may not) include the fact of already being of the identity datatype.
Hope this brings fresh air to the so called debate - I know it's almost impossible to end it 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.