>Steve,
> Just to chime in here. Yes John is correct. All SQL Server tables should have a clusted index.
>
>The rules of normalization require that all tables have primary keys. With this rule in mind: SQL Server uses indexes for primary keys. Even if you dont specify one SQL Server creates one in the background. So by default you are going to be creating an index.
>
>With this in mind:
>

>>SQL Server will not fully optimize a query without a primary key. This is just the way SQL Server behaves. <<

>I have found muliple times to my chagrin that queries perform abyssmially slow without a clustered index. Yes there is an index but if it is not clustered kiss performace goodbye. Add a clustered
>index and the queries run orders of magnitude faster.
>
>So if you are going to go to the trouble of creating a primary key you should also make sure that the index is clustered.
>
>Also: you don't need to create a clusted index on primary key. You usually choose to create a clustered index on that column. However, if you access data via another column ( a credit card number, a social security number, etc) then you could consider that as a primary key.
>
>
>Rodman

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