“Effective Clustered Indexes” (Think “Clustered Indexes in-depth from a real world, major SQL Server shop, DBA”)

simple-talk - Learn SQL Server  -  Effective Clustered Indexes

“As a gridline, clustered Indexes should be Narrow, Unique, Static and Ever Increasing (NUSE).  Michelle Ufford Explains why.

Clustered indexes are the cornerstone of good database design. A poorly-chosen clustered index doesn't just lead to high execution times; it has a 'waterfall effect' on the entire system, causing wasted disk space, poor IO, heavy fragmentation, and more.

This article will present all the attributes that I believe make up an efficient clustered index key, which are:

• Narrow – as narrow as possible, in terms of the number of bytes it stores
• Unique – to avoid the need for SQL Server to add a "uniqueifier" to duplicate key values
• Static – ideally, never updated
• Ever-increasing – to avoid fragmentation and improve write performance

By explaining how SQL Server stores clustered indexes and how they work, I will demonstrate why these attributes are so essential in the design of a good, high-performance clustered index

…

Summary

In this article, I've discussed the most desirable attributes of a clustered index: narrow, unique, static, and ever-increasing. I've explained what each attribute is and why each is important. I've also presented the basics of B-tree structure for clustered and non-clustered indexes. The topic of "indexing strategy" is vast topic and we've only scratched the surface. Beyond what I presented in this article, there are also many application-specific considerations when choosing a clustering key, such as how data will be accessed and the ability to use the clustered index in range-scan queries. As such, I'd like to stress that the attributes discussed in this article are not concrete rules but rather time-proven guidelines. The best thing to do if you're not sure if you've chosen the best clustering key is to test and compare the performance of different strategies.

…”

I liked that this article was the fact that it was a “From a DBA for DBA’s” article, that the author lives and breaths SQL Server in her day to day life in a major way (Michelle Ufford is a Senior SQL Server Developer at GoDaddy.com). That’s pretty “real world” in my book…

Also I really liked the presentation style and how the two usual key types, ID/Int and UniqueIdentifier/GUID, were used (Please, lets not get into a debate about those key types, there’s good and bad for both and the answer to the “right” type is usually “it depends…” ;)