Reasons not to have a clustered index in SQL Server 2005

Question

I've inherited some database creation scripts for a SQL SERVER 2005 database.

One thing I've noticed is that all primary keys are created as NON CLUSTERED indexes as opposed to clustered.

I know that you can only have one clustered index per table and that you may want to have it on a non primary key column for query performance of searches etc. However there are no other CLUSTERED indexes on the tables in questions.

So my question is are there any technical reasons not to have clustered indexes on a primary key column apart from the above.

Answer 1

On any "normal" data or lookup table: no, I don't see any reason whatsoever.

On stuff like bulk import tables, or temporary tables - it depends.

To some people surprisingly, it appears that having a good clustered index actually can speed up operations like INSERT or UPDATE. See Kimberly Tripps excellent The Clustered Index Debate continues.... blog post in which she explains in great detail why this is the case.

In this light: I don't see any valid reason not to have a good clustered index (narrow, stable, unique, ever-increasing = INT IDENTITY as the most obvious choice) on any SQL Server table.

To get some deep insights into how and why to choose clustering keys, read all of Kimberly Tripp's excellent blog posts on the topic:

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Clustering-Key.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Clustered-Index.aspx

Excellent stuff from the "Queen of Indexing" ! :-)

Answer 2

Clustered Tables vs Heap Tables

(Good article on subject at www.mssqltips.com)

HEAP Table (Without clustered index)

• Data is not stored in any particular order

• Specific data can not be retrieved quickly, unless there are also non-clustered indexes

• Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages

• Since there is no clustered index, additional time is not needed to maintain the index

• Since there is no clustered index, there is not the need for additional space to store the clustered index tree

• These tables have a index_id value of 0 in the sys.indexes catalog view

Clustered Table

• Data is stored in order based on the clustered index key

• Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns

• Data pages are linked for faster sequential access Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES

• Additional space is needed to store clustered index tree These tables have a index_id value of 1 in the sys.indexes catalog view

Answer 3

Please read my answer under "No direct access to data row in clustered table - why?", first. Specifically item [2] Caveat.

The people who created the "database" are cretins. They had:

• a bunch of unnormalised spreadhseets, not normalised relational tables
• the PKs are all IDENTITY columns (the spreadsheets are linked to each other; they have to be navigated one-by-one-by-one); there is no relational access or relational power across the database
• they had PRIMARY KEY, which produce UNIQUE CLUSTERED
• they found that that prevented concurrency
• they removed the CI and made them all NCIs
• they were too lazy to finish the reversal; to nominate an alternate (current NCI) to become the new CI, for each table
• the IDENTITY column remains the Primary Key (it isn't really, but it is in this hamfisted implementation)

For such collections of spreadsheets masquerading as databases, it is becoming more and more common to avoid CIs altogether, and just have NCIs plus the Heap. Obviously they get none of the power or benefits of the CI, but hell, they get none of the power or benefit of Relational databases, so who cares that they get none of the power of CIs (which were designed for Relational databases, which theirs is not). The way they look at it, they have to "refactor" the darn thing every so often anyway, so why bother. Relational databases do not need "refactoring".

If you need to discuss this response further, please post the CREATE TABLE/INDEX DDL; otherwise it is a time-wasting academic argument.