DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to create a fast clustered index on a table?

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,

      [StartDate] 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

 

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE