Take the 2-minute tour ×

What is wrong in code. or its results, illustrating that clustered index is evil [1]?
and how to de-debunk, i.e. to return to customary myths and best practices?

Debunking myths about clustered indexes - part 3 (example script)

Edit by gbn, Jan 2012

The dead link used to have a script that "proved" clustered indexes were bad.

Similar SO question: http://stackoverflow.com/questions/4034076/reasons-not-to-have-a-clustered-index-in-sql-server-2005

share|improve this question
add comment

migrated from stackoverflow.com Jan 26 '12 at 14:33

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 7 down vote accepted

That script has a fairly wide, varchar clustered index. And it needs an index rebuild after populating it with random data too: you'll have massive fragmentation.

A good clustered index is narrow, numeric and strictly monotonically increasing: which is why folk use surrogate keys...

A table without a clustered index is called a "heap" because it exactly that: a pile of data lying on disk. And it will stay that way no matter how much you rebuild your NC indexes. Outside of something like staging tables (with a load/truncate usage pattern) there is no reason not to have a clustered primary key.

Edit: The link does not debunk a clustered index myth, but rather shows how to create an unsuitable clustered index and why index maintenance is important. Parts 1 and 2 mentions bookmark lookups (now key lookups in SQL Server 2005+): a good NC index will be covering so they don't happen.

To learmn about indexes, I suggest Simple Talk's many articles. Like this one

share|improve this answer
+1 for Simple-Talk, and of course - also check out Kim Tripp's long list of blog posts on the topic: sqlskills.com/BLOGS/KIMBERLY/category/Clustered-Index.aspx –  marc_s Nov 7 '10 at 20:53
add comment

Clustered indexes aren't evil. However, not every table will benefit from a clustered index and they are often carelessly misused in ways that are detrimental. So the point of Gregg's (and others') articles is to demonstrate some of the pitfalls.

share|improve this answer
add comment

Your Answer


