Steve..

Self-referencing tables are a handy tool to employ. Going 1 level deep (supervisor - employee) for example, is managable. A scenario where the table can grow in a way you describe, with a possibility of infinite nesting, takes things way out of the norm. Basically, you are implementing a hierarchical model in an environment that is optimized for the relational model.

Having a single table that could grow like this, as opposed to dividing the data up into separate tables, does not seem like an optimal approach to me. And, why would you elect to have so many reads/writes against a single table as opposed to dividing up accross multiple tables?

Since you have elected to implement a hierachical model in a relational space, you are not using a tool for the way it was intended to be used (square peg, round hole). I am not surprised by the issues you have probably run into...

To clarify, implementing hierachical concepts in a relational setting is not uncommon. Rather, I think the issue has to do with the amount of reads/writes against a single table. On its face, I question that design, especially from a scaleability standpoint. How will this work 18 months from now? Will it scale? To date, you have not convinced me to eschew the rule that clustered indexes should be employed. At best, you have offered anecdotal evidence, based on a very, very narrow set of circumstances, that clustered indexes should not always be employed.

Another issue to look at is the level of hardware employed relative to the problem being solved.





>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