Hi John,

What is the clustered index? IOW, if you are clustering on a key that is highly random during bulk inserts then, bulk inserts will be slower, due to the number of pages split. Are the preponderance of queries based on the clustered index? If not, you may want to have another look at waht actually gets indexed. It's NOT always the primary key that gets the clustered index.

- Val Matison

>Jim
>
>Thanks for the words, that's really helpful.
>
>We get specialist SQL help when we implement databases. It saves so much angst. We can't all be experts on everything. I guess that's like a family physician calling in an orthopaedic surgeon to fix a wrist fracture. The family physician can almost certainly fix it... but why?
>
>FWIW, we have some tables without clustered indexes. These are tables whose massive insert load is intended for the rare event of access audit... perhaps once a month. The table gets insert hammered hard. Expert advice included "real life" testing on the aging server with real loads. From memory the difference with and without clustered index was measurable- worse with the clustered index.
>
>My habit is to use a clustered index in SQL Server, for reasons such as those you cite. That I do it does not make it correct... the issue you raise about index on deleted() is illustrative of how one can "know" something is true because "everyone knows" it is true. I'm glad people are brave enough to question, examples where people do not use clustered indexes are very helpful for me.
>
>Regards
>
>JR