Hey Rod,

>> All SQL Server tables should have a clusted index.

Nope. Sorry <s>. Here's a perfect example, one we work with every day.

It's a self-referencing document table, where the relationship is the traditional n-level hierarchy: book-volume-chapter-section... The n-levels can be near infinite in nesting but, in practice, it's usually less than 8.

The table contains a hundred books, potentially several hundred books eventually. This means several thousand chapters, millions of paragraphs, which are our unit of strage. Records are being inserted, deleted, and updated by hundreds and potentially thousands of concurrent authors and editors online.

This authoring and editing typically happen with "chapter" or "section" granularity -- hundreds of paragraphs (records) swapping in and out at once.

Were that not enough, users can bring-in many many records on demand either by reactivating dormant books from archive, or by restoring redlined versions of chapters from recent archives to compare and rollback changes made by other authors and editors.

Also due to the nature of the application, inserts and updates into this table are always delete and insert. There are also continuous processes that update the text data in-situ, like spell checkers and HTML/XML validators, and this is of course pure single-record seek and update stuff.

And another little twist: Document records can belong to more than one parent. I'm not sure if this tidbit matters or not but it's an interesting twist.

We don't have a clustered index on this table, Rod. This table is highly transactional, it grows quasi-organically like a tree, the records themselves are prone to grow as chunks of text are added to paragraphs, and records always arrive in batches of potentially hundreds of records at a time. Save and update times on this table are greatly hindered by both explicit and implicit clustering. That's because the server spends more time managing overflow pages and fragmentation than actual work for almost every operation thrust upon it.

In short, this sort of table calls for a much different index paradygm.

Therefore, the statement "All SQL Server tables should have a clustered index" is demonstrably incorrect. It's a rule of thumb, a reasonable heuristic, but there are exceptions, such as the one I give above.

Now I recognize that for banking or financial application, and corporate stuff generally, "all SQL Server tables should have a clustered index" is true. That's because the corporate world is typically a flat, normalized problem-space. But that statement is certainly not true in some non-corporate data spaces.



**--** Steve


>Steve,
> Just to chime in here. Yes John is correct. All SQL Server tables should have a clusted index.
>
>The rules of normalization require that all tables have primary keys. With this rule in mind: SQL Server uses indexes for primary keys. Even if you dont specify one SQL Server creates one in the background. So by default you are going to be creating an index.
>
>With this in mind:
>
>SQL Server will not fully optimize a query without a primary key. This is just the way SQL Server behaves. I have found muliple times to my chagrin that queries perform abyssmially slow without a clustered index. Yes there is an index but if it is not clustered kiss performace goodbye. Add a clustered
>index and the queries run orders of magnitude faster.
>
>So if you are going to go to the trouble of creating a primary key you should also make sure that the index is clustered.
>
>Also: you don't need to create a clusted index on primary key. You usually choose to create a clustered index on that column. However, if you access data via another column ( a credit card number, a social security number, etc) then you could consider that as a primary key.
>
>
>Rodman