pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

8/23/2015

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

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 
“NUNS” 
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!

No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.