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!
