The fastest clustered index is on an identity column with seed (1,1). This is the default when creating a table.

If you make the identity column a primary key in SSMS Object Explorer table designer, it creates a unique clustered index automatically. Primary Key does not require clustered index, that is optional.

A column(s) used in frequent range searches should have the clustered index for best performance. Example for non-PK clustered index in AdventureWorks2008:

CREATE UNIQUE CLUSTERED INDEX [AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate]

ON [Production].[BillOfMaterials] (

      [ProductAssemblyID] ASC,

      [ComponentID] ASC,

The following Microsoft SQL Server T-SQL script also creates a unique clustered index on ProductID and unique non-clustered index on ProductName:

CREATE TABLE Product (

ProductID INT IDENTITY(1,1) PRIMARY KEY,

ProductName varchar(64) UNIQUE,

Price money,

ModifiedDate datetime default (getdate()));

The following Microsoft SQL Server T-SQL scripts demonstrate the advantages of CLUSTERED INDEX for range searches.

------------

-- Compare CLUSTERED and NON-CLUSTERED index performance for RANGE searches

------------

-- NON-CLUSTERED INDEX on OrderDate

USE tempdb;

SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader

ALTER TABLE SOH ADD CONSTRAINT pksoh PRIMARY KEY (SalesOrderID)

UPDATE SOH SET OrderDate=DATEADD(SECOND,SalesOrderID/7,OrderDate) -- add time part

CREATE INDEX idxODate on SOH(OrderDate)

DBCC DROPCLEANBUFFERS

DECLARE @Start datetime = CURRENT_TIMESTAMP

SELECT * FROM SOH WHERE OrderDate >= '2003-12-01' AND OrderDate < '2003-12-02'

SELECT Timing = DATEDIFF(millisecond, @Start, CURRENT_TIMESTAMP)

-- 213 msec 

DROP TABLE SOH

GO

-- CLUSTERED INDEX on OrderDate

USE tempdb;

SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader

ALTER TABLE SOH ADD CONSTRAINT pksoh PRIMARY KEY NONCLUSTERED (SalesOrderID)

UPDATE SOH SET OrderDate=DATEADD(SECOND,SalesOrderID/7,OrderDate)

CREATE CLUSTERED INDEX idxODate on SOH(OrderDate)

DBCC DROPCLEANBUFFERS

DECLARE @Start datetime = CURRENT_TIMESTAMP

SELECT * FROM SOH WHERE OrderDate >= '2003-12-01' AND OrderDate <  '2003-12-02'

SELECT Timing = DATEDIFF(millisecond, @Start, CURRENT_TIMESTAMP)

-- 63 msec

DROP TABLE SOH

GO

------------

Related articles:

Using Clustered Indexes

Clustered Index Design Guidelines

An Introduction to Clustered and Non-Clustered Index Data Structures