You're welcome, Vin.

If it's working OK for you, I'd just leave it and keep this stuff in mind for next time.

While there may be something special about GUIDs, I should also point out that the book also mentioned that SQL Server will, internally, make every clustered index key unique by appending a sequential suffix to any key that is a duplicate.

Cheers

>Jim -- thanks for the citation. In the case I am thinking of, our (admittedly quick and dirty) benchmark of INSERTs conflicted with what you report. A series of 500k inserts against an existing 3 million record table was much slower when the index was clustered (the inserts were GUIDs, not a lower-frequencyu field such as diagnosis or gender or whatever). If I have some time next week, I will re-examine what we found, to see if something else was going on. To my knowledge, the only difference was having an index clustered vs. non-clustered.... but as we all know, other variables sometimes sneak in.
>
>
>
>
>>SNIP
>>>But the loss in speed on INSERTs would just kill us.
>>
>>Vin,
>>
>>I really should stay out of this since the closest that I've come to actually doing SQL Server is a MSDE job.
>>
>>Nevertheless, everyone commenting on clustered index seems to relate to the high pain in re-writing frequently due to INSERTs.
>>I didn't read things as happening that way in the product. In "Inside Microsoft SQL Server 7.0" pp90 it says "Since SQL Server maintains ordering in index leaf levels, you do not need to unload and reload data to maintain clustering properties as data is added and moved. SQL Server will always insert rows into the correct page in clustered sequence. For a clustered index, the correct leaf page is the the data page in which a row will be inserted". On pp 422 of the same book "New rows are inserted into their clustered positions, splicing in a page via a page split if the current page has no room. . . . Because the clustered index dictates a particular ordering for therows in a table, every new row has a specific location where it belongs. If there's no room for the new row on the page where it belongs, you must allocate a new page and link it into the list of pages. If possible, this new page will be allocated from the same extent as the other pages to which it will be linked. If the
>>extent is full, a new extent is allocated to the object.".
>>So I sure do NOT see a HIGH price for clustered indexes with lots of inserts, based on the above.
>>
>>While I'm here let me also point out that the following appears at least TWICE in the book "Most tables should have a clustered index. If your table will have only one index, it generally should be clustered." - on page 247 and on page 774. On page 774 it also says "Because of the clustered index's role in managing space within the table..." before the quote above.
>>
>>This strongly implies to me that NOT using a clustered index is the RARE exception.
>>
>>As someone who abhors "rules" that state categorically only one option as THE valid option, I would encourage anyone contemplating this issue STILL think it through for each and every table. But I bet that, most of the time, they will come up with the conclusion that a clustered index makes sense.
>>
>>2¢
>>Jim Nelson