What is wrong in debunking myths about clustered indexes?

Question

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?

[1]
Debunking myths about clustered indexes - part 3 (example script)
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/16/377.aspx

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

Answer 1

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

Answer 2

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.