Rod,

The performance hit with clustered index, and it's a bad one, occurs whenever a save operation occurs on the document table. A document save is typically for several dozen records, potentially hunderds of records, at a time.

When this data is static, as it is when a book is in the archive database, it's a different story. Here all we do is query the archive for ranges of records; perfect conditions for a clustered index. But for the OLTP side of this app, the background maintenance of the clustered index isn't worth the pain.

It might be helpful to know that there are many things that happen upon save. Among them:
* old document records and associated structure relationship records (in a companion table) get shipped to an authoring archive database
* old document records and associated structure relationship records are deleted
* new document records and associated structure relationship records are inserted
* the save event is logged
* typically the whole chapter or topic is queried for display to the user, just as you might expect for a document save operation.

The extra overhead on the backend to physically manage the cluster is expensive enough, but it's also not the only thing going on in a typical "document save" Use Case, and with hundreds of simultaneous users pounding multiple concurrent updates, the clustered index management is just far too much overhead.

**--** Steve


>Steve,
> What is the performance problem you have with a clustered index. 800,000 is a small number of rows for SQL server to munch on.
>
>Have you tried a clustered index and if so what performace problems did you experience ?
>
>Also, I believe that whenever SQL Server does an UPDATE it DELETES and INSERTES the data. In SQL Server triggers you get two internal cursors, inserted and deleted. Disclaimer: this is my impression of how it works.
>
>
>Rodman