database design - How to choose the clustered index in SQL Server?

Question

Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (Meaning of Primary Key to Microsoft SQL Server 2008) has shown that it is not necessary to set PK and clustered index to be equal.

So how should we choose clustered indexes then? Let's have the following example:

create table Customers (ID int, ...)
create table Orders (ID int, CustomerID int)

We would usually create the PK/CI on both ID columns but I thought about creating it for Orders in CustomerID. Is that the best choice?

Answer 1

According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:

• Unique
• Narrow
• Static

And if you can also guarantee:

• Ever-increasing pattern

then you're pretty close to having your ideal clustering key!

Check out her entire blog post here, and another really interesting one about clustering key impacts on table operations here: The Clustered Index Debate Continues.

Anything like an INT (esp. an INT IDENTITY) or possibly an INT and a DATETIME are ideal candiates. For other reasons, GUID's aren't good candidates at all - so you might have a GUID as your PK, but don't cluster your table on it - it'll be fragmented beyond recognition and performance will suffer.