datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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. Junction tables usually benefit from covering index by avoiding bookmark lookups.

Keep covering index as narrow as possible. Wide covering index is counter productive.

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

-- 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

 

Exam Prep 70-461
Exam 70-461