Sunday, August 23, 2015

Actual Emails: Allow NUNS to Lead You to Good Clustered Index Design

Much more on this topic at the Sparkhound blog.

Wrote this email exchange with a colleague who wanted to confirm that the client-proposed design for a table was... less than optimal.

Subject: clustered index
From: A. Developer

Hey William,
I believe the client created this Clustered Index... <horrifying screenshot of a clustered index with many large nvarchar columns as the key>

From: William 

The clustered index is ideally 
1)      Non-changing
2)      Unique
3)      Narrow
4)      Sequential 
Having those multiple nvarchar columns in the clustered index is probably not a good idea, as it violates #3 and probably #1 and #4 too.  
The most ideal clustered index is on an integer identity column. You can modify the design of a table to add one of those. 
Look at the data and suggest a new clustered index. That five-column clustered index might be a perfectly fine nonclustered index, but is an inefficient clustered key.

From: A. Developer
That makes sense. Thanks William!