I agree that a clustered index should be based on how you normally query records from the table.

However, it has been our (Rod and I) experence, that when a clustered index does not exist and we then create a clustered index on the PK, the query performance greatly improves. This is not coming from a book, this comes from the body of past experience.

Unless you are going to take the time to have non-clustered indexes that match your queries, you should have a clustered index.

This issue does not rest with returning the least amount of data. That is an after the fact issue. The issue is to eliminate table scans.

The query optimizer greatly favors a clustered index. That is an issue you need to contend with. The issue is not whether you have a clustered index or not, the issue is what comprises the clustered index. 99.999% of the time, you will need records physically ordered in a way that is different than how data is entered into the tables. The question is how detailed the clustered index needs to be in order for non-clustered indexes to make use of that clustered key. The clustered index should not be too detailed. On the other hand, it should not be too general. Because the optimizer wants one, it is important to have one.




>*snip*
>>Conclusion: Every table in SQL Server should have a clustered index. In the absence of knowing which key to choose, you then go with the PK.
>>
>
>John,
>I disagree with this entirely. IMO, clustered indexes are created with a purpose. If no purpose exists then creating one is superfluous.
>
>Clustered indexes physically order the table. The reason for them IMO, is to create blocks of data. Returning the least amount of pages of data is key to performance. If the data you are returning is ordered so tht the block(s) you need are located on a single page or consecutive pages, BINGO! Your job is done.
>
>However, if your queries are going to be drawing data from all over the table, a clustered index is not necessary. And in fact, if you base a clustered index on an auto incrementing, surrogate key, in essence you get the same ordering as if you had no clustered index at all. The table is still ordered in the order in which the row was added to the table. What benefit is there? An index scan of a clustered index based on this is no better than a table scan (it's the same).
>
>No, my conclusion is a bit different.
>
>Design your clustered indexes around your queries. If this is not possible for whatever reason (e.g. CRUD operation performance), don't use them.