I am resurrecting an old thread... but if you were following it, this is an important postscript.

A few weeks ago, I made the argument that in some cases, a table should NOT have a clustered index. In particular, I made the case that an intermediate table for a many-to-many join, for cases where queries are quite infrequent, may not require such an index. In fact, I argued, it may be better off *without* such an index -- because INSERT performance would improve.

This caused a bit of debate on the real cost of a clustered index in mass INSERTs. (And remember, this was an audit table that rarely, if ever, needs querying -- please don't comment on this issue, return instead to some earlier threads -- the query-side isn't the point of this thread -- I am talking purely about INSERTs!) In the end, I offered a script that benchmarked indexes and showed how much slower the inserts were with a clustered index.

Well, I investigated further and talked with a SQL Server MVP and found that my benchmark tests OVERSTATED this difference. Here's why -- inserting a row into a table with a clustered index will indeed require a reshuffling of data -- but only on the particular page into which the record was inserted. My example included multiple iterations around a narrow table. As a result, there simply were not that many pages (and possible only one) being shuffled!

Instead of testing X iterations against N records, I should have tested ONE iteration against X*N records. The performance hit would have been the equivalent of the hit from just my FIRST iteration -- taken against the whole, this is a very small percentage. In the case I used, it looked much larger.

Three lessons learned:

1) INSERTS against tables with clustered indices DO INDEED reshuffle data, despite the claims of some folks that they do not;
2) These INSERTS shuffle data on only ONE page, not the entire table, despite the claims and concerns of folks like me!;
3) Next time I benchmark ANY process in SQL Server, I am making sure that the experiment spans hundreds of pages, not just a few. I was thinking-Fox, not thinking-SQLServer.