Larry,

One important thing you're forgetting about here is that non-clustered indexes store a pointer to the location that record can be found. If the table has a clustered index, this is used as the pointer. If the table is a heap, it uses an RID to locate the record. (File#:Page#:Slot#)

I know what you're thinking already: how many times does the user know about the primary key or clustered index? The answer is they don't; but you're program does. Any time the user requests a single record for editing or even display or join purposes, guess what? Better to use a clustered index on the primary key. That way, once SQL Server finds it, the data is right there: it won't have to search through an index to find the pointer of where to go find that information.

So in essence, any time you deal with surrogate keys, you'll want a clustered index. By virtue of the software systems we design as developers, that creates the "purpose" you make reference to. Whether you need to retrieve one record to display for a user or whether you perform a JOIN with other tables, a clustered index will improve data-retrieval performance.

>*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.