I am not arguing the issue of whether or not you should have a primary key, I was just pointing out that there are exceptions to this rule as written in Rod Paddock's reply where SQL Server can optimize a query without the need of a primary key.

There is no rest of the story with respect to covered queries, you don't need to have a clustered index for a covered query to be optimized.

That was the only point I am making in the thread, and as I said there I agree with that and will further state that primary keys and / or clustered indices are useful to most queries.

>A few points...
>
>First, every table should have a primary key...period. Second, if you have a clustered index, any non-clustered index can make use of the clustered key. i.e., the optimizer is going to favor the use of a clustered index. The important issue not whether you have a clustered index or not...you should. Rather, the issue is which column(s) the clustered key is comprised of.
>
>The information you cite is correct, at least partially with respect to the context. However, your synopsis leaves out the rest of the story. If you don't have a clustered index, you are forced to have indexes that match your queries. This can have the effect of making a copy of the table. If instead, you have a clustered index, you can reduce the size of the non-clustered indexes since the optimizer will automatically use the clustered index in queries. Pages 776-77 of Inside SQL Server does a good job of explaining why Clustered Indexes, in the final analysis, are a good idea and should be used.
>
>What Kalen writes about on page 777 is the effect of a unique index. Even if you don't have a formal primary key, a unique index will have the same practical impact. I suppose it is a substance over form argument. The more detailed your index is, the more unique it is likely to become. This leads to the classic surrogate vs. meaningful key argument. Single value, auto-numbered surrogate keys are a better way to go for this reason. They are less complex and offer the most efficient way of uniquely identifying rows. And, they are immutable with respect to changes in the data.
>
>On page 819 in Inside SQL Server, is a table that summarizes index costs among the different types of indexes in SQL Server. It is clear the reason why a non-clustered covering index works is because all the necessary information is in the key. Essentially, one is over-compensating for the fact that a clustered index does not exist. Forcing to have all values in the key is an expensive proposition.
>
>Page 775 does a good job of explaining why non-clustered indexes have to be highly selective. The second paragraph is directly on point when the fact that the query optimizer GREATLY favors clustered indexes over their non-clustered counterparts.
>
>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.
>
>
>
>>I would tend to agree with most of what you say about primary indices, although there is an exception to the comment I marked above: any time an index contains all referenced columns of a query SQL Server does not need to go to the data pages and thus does not need a primary key. This is known in SQL Server as a covering index and it can be non-clustered. Such queries are fully optimized without the need of a primary key or clustered index.
>>
>>I will cite the Inside SQL Server book and also a recent article in SQL Server magazine by Kalen Delaney. Just in case :)
>>
>>Just my .02