SQLUSA.com
SQL SERVER 2008 GRAND SLAM
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL JOBS SQL Server Training Scripts JOB BANK
  SQL Server 2008 Training Scripts  
  SQL Server 2005 Training Scripts  
News SQL Server Articles SQL Format

How to create a covering index?

Execute the following Microsoft SQL Server T-SQL scripts to demonstrate two ways to cover a query with index for boosting performance. Covering index has increased size, as a result it may slow down other queries. Therefore careful tests are necessary to judge its benefits. Very high frequency and business critical queries are prime candidates for index covering.

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

-- 100 fold estimated query cost reduction with covering index

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

USE AdventureWorks2008;

-- Cost without covering index: 0.576

SELECT Date=convert(date,OrderDate), TotalDue, SubTotal, TaxAmt, Freight

FROM Sales.SalesOrderHeader

WHERE OrderDate >='2004-02-01' AND OrderDate < '2004-02-05'

 

-- Create covering index with INCLUDE

CREATE INDEX idxOrdDtDue ON Sales.SalesOrderHeader(OrderDate)  -- key column

       INCLUDE(TotalDue, SubTotal, TaxAmt, Freight)            -- non-key columns

 

-- Cost with covering index: 0.0052

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

 

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

-- Covering Index: Testing Composite Index option - SQL Server all versions

------------
USE Northwind

 

-- CLUSTERED INDEX SCAN on Primary Key index

-- COST 0.0056

SELECT City

FROM   Customers

WHERE  Country = 'UK'

 

-- The following covering index (composite index) can be created:

CREATE INDEX IDXLocation ON Customers ( Country, City)

GO

 

-- Index Covering improves SQL Server query performance

-- INDEX SEEK on the new covering index - SQL Server optimization

-- COST 0.0032

SELECT City

FROM   Customers

WHERE  Country = 'UK'

GO

DROP INDEX Customers.IDXLocation

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

 

-- Covering one table in multi-table query

SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit,

       Products.UnitsInStock, Products.Discontinued

FROM Categories INNER JOIN Products

       ON Categories.CategoryID = Products.CategoryID

WHERE Products.Discontinued <> 1

ORDER BY Categories.CategoryName, Products.ProductName

GO

-- Cost: 0.0303

CREATE INDEX idxProduct ON Products(CategoryID, Discontinued)

       INCLUDE(ProductName, QuantityPerUnit, UnitsInStock)

-- Cost: 0.0198

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

 

 

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

-- Testing INCLUDE index option - SQL Server 2005 & SQL Server 2008
-- Improve query performance with SQL Server 2005 and on covering index enhancements

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

USE AdventureWorks2008;

GO

 

-- Drop existing index just for the testing

IF EXISTS (SELECT *

           FROM   sys.indexes

           WHERE  object_id = OBJECT_ID(N'[Production].[Product]')

                  AND name = N'AK_Product_ProductNumber')

  DROP INDEX [AK_Product_ProductNumber] ON [Production].[Product] WITH ( ONLINE = OFF )

GO

 

-- CLUSTERED INDEX SCAN on ProductID

-- COST 0.012

SELECT ProductNumber,

       ProductName = Name,

       ListPrice,

       Color

FROM   Production.Product

WHERE  ProductNumber LIKE 'FR-%';

GO

 

-- Create a covering index with non-key columns INCLUDE-d
-- Only the leaf level of the index tree is effected as opposed to

-- all levels in a composite index, hence it is faster than the composite index

CREATE NONCLUSTERED INDEX idxProductNum ON Production.Product (

      ProductNumber)

INCLUDE (Name,ListPrice,Color);

GO

 

-- NON-CLUSTERED INDEX SEEK on the new index - SQL Server index optimization

-- COST 0.0041

SELECT ProductNumber,

       ProductName = Name,

       ListPrice,

       Color

FROM   Production.Product

WHERE  ProductNumber LIKE 'FR-%';

GO

 

DROP INDEX dxProductNum ON Production.Product;

GO

 

-- RECREATE the unique index which was dropped for testing

CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber]
ON [Production].[Product] (

      [ProductNumber] ASC)

WITH (PAD_INDEX = OFF,

      STATISTICS_NORECOMPUTE = OFF,

      SORT_IN_TEMPDB = OFF,

      IGNORE_DUP_KEY = OFF,

      DROP_EXISTING = OFF,

      ONLINE = OFF,

      ALLOW_ROW_LOCKS = ON,

      ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

 

GO

 

EXEC sys.sp_addextendedproperty

  @name = N'MS_Description' ,

  @value = N'Unique nonclustered index.' ,

  @level0type = N'SCHEMA' ,

  @level0name = N'Production' ,

  @level1type = N'TABLE' ,

  @level1name = N'Product' ,

  @level2type = N'INDEX' ,

  @level2name = N'AK_Product_ProductNumber'

 

GO

Related article link: SQL Server Optimization

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page
SQL Server Training at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Microsoft SQL Server 2000 Training Videos at www.sqlusa.com.

FREE SQL & Business Intelligence / OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011
Invest in Your SUCCESS!

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts


Copyright 2005-2011, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.