>
>So - considering all of the above, why would one chose natural keys for a clustered primary key? That seems to me to be worse than any of the other scenarios above.
>
>Thanks for any guidance or clarification.

Hi, Mike,

As you stated (or at least implied above), if you use a natural key for a clustered index, and that natural key doesn't follow a sequential order, you'll likely have many data page splits and fragmentation will accelerate. Performance starts to degrade after about 30% fragmentation, and I've seen severe neglect where fragmentation is over 90%. Obviously, you can rebuild the indexes overnight, but I've seem fragmentation accelerate during the day.

I've seen people use random GUIDs for clustered indexes and that is a very questionable practice.

The general rule for a clustered index (if you plan to do something beyond an identity or sequence object value) is that the stream of values is one where you're frequently querying on a wide range of those values, and in that order. If the ORDER BY when querying that table is the same order as the values you defined for the clustered index, SQL Server's optimizers might deem that the ORDER BY isn't needed. But obviously, that's something you need to be sure and verify.

As a general rule, don't use values for a clustered index that can be updated. Also, any "wide definition" for the value is generally discouraged.

Bottom line, my two cents has always been that if a clustered index is going to be something other than the identity value, there needs to be a compelling reason. I'm not a DBA by trade but that's always been the direction I've gone.

There's some information on MSDN regarding clustered index design guidelines as well.

Also...if you're using SQL 2012 or above, take a look at the sequence object. It's basically a table-independent identity value. Once you use it, you'll never want to use a sequential identifier again.


Hope that helps...