Covering Indexes , Create , execute in SQL Server


The Microsoft SQL Server T-SQL create index has the INCLUDE option (2005 and on) to include non-key column(s) in the index so that a specific query can be satisfied – covered – from the index without accessing table data. Composite index can also cover a query. Covering index speeds up the target query, however, due to larger size, may have negative impact on other queries.

— MSSQL query with execution timing envelope
— DBCC DROPCLEANBUFFERS forces pages to disk for consistent time measurements
— SELECT INTO table create
USE tempdb;
SELECT * INTO Address
FROM AdventureWorks2008.dbo.Address
 
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— TABLE SCAN (NonClustered)
— COST 0.275
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 340
— 76 msec
 
 
— T-SQL create index with non-key columns INCLUDE
— MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCovering
ON dbo.Address (PostalCode)
INCLUDE ( City, StateProvinceID);
GO
— Command(s) completed successfully.
 
 
— SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— INDEX SEEK (NonClustered)
— COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 4,
— 10 msec
 
 
DROP INDEX dbo.Address.idxPostalCodeCovering
GO
 
— T-SQL create composite index
— MSSQL SELECT will be covered for City & StateProvinceID (not for other colums)
CREATE INDEX idxPostalCodeCoveringComposite
ON dbo.Address (PostalCode,City, StateProvinceID);
GO
— Command(s) completed successfully.
 
 
— SQL Server time query again after index create
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate();
 
— INDEX SEEK (NonClustered)
— COST 0.0034
SET STATISTICS IO ON
SELECT a.PostalCode,
       a.City,
       a.StateProvinceID
FROM   dbo.Address a
WHERE  a.PostalCode LIKE ‘34%’;
SET STATISTICS IO OFF
 
SELECT DurationMSEC = DATEDIFF(ms, @StartTime, getdate())
GO
— logical reads 4,
— 13 msec
 
 
— Cleanup
DROP TABLE dbo.Address

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: