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
Rod Paddock
Editor in Chief CoDe Magazine
President Dash Point Software, Inc.
VP Red Matrix Technologies,Inc.