Eric, Im doing a project using MicroSoft SQLServer 7.0 for a cellphone companys international calls. Consulting SQLServers Books Online on Clustered Indexes, I came across these rather differing notes:

 From Index Tuning Recommendations: Use integer keys for clustered indexes. Additionally, clustered indexes benefit from being created on unique, nonnull, or IDENTITY columns. For more information, see Using Clustered Indexes.

 From Using Clustered Indexes: Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried that way rather than by employee ID.

Is there any point of reconciliation for the 2 items given above?

Thanks, Chelo

-- Anonymous, January 26, 2000

Answers

Chelo,

Steve Robinson, SQL Server MVP, sums it up nicely in microsoft.public.sqlserver.programming:

"In reality a primary key should always be unique and the fact that you will refence this key in an exact match means you do not really need a clustered index. The basic rule of thumb is if you are only going to have one index on a table, make it clustered as this will help with space utilization and the hit will incur one less I/O because because you will hit the actual data pages. If you are going to have more than one index and, say, one is on surname, you would place the clustered index on surname and the non clustered on the primary key - you would incur one more hit on the primary key exact match but when searching for surnames like "smit%" you would be using a clustered index to find the start and SQL Server would start reading the pages sequentially from that point on as the table would be in sorted order."

Hope this helps,

Eric

-- Anonymous, January 27, 2000