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 covering index?

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

 

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