Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 208

Getting ready

To perform a covering non-clustered index, it is necessary to find out which other columns
need to be there in the non-clustered index. It is not rocket science to decide this; one has
to go through with different options and check the execution plan for the best-suited match.

By looking at Key Lookup in an execution plan, we get to know that it is going to the clustered
index, which is present on the RefNo column, to find out OrderID . So let us cover the
OrderID field in the non-clustered index and see the difference.

How to do it...

Perform the following steps to see the usage of the covering index:

1. Drop the previously created non-clustered index:

--Dropping the previously created non-clustered Index
DROP INDEX idx_orderdate ON ordDemo
GO

2. Create a non-clustered index by covering one more column, OrderID , within it:

--recreating a non-clustered Index with OrderDate and OrderID
--by sorting OrderDate in descending order in Index
--and sorting OrderID in ascending order in Index
CREATE NONCLUSTERED INDEX idx_orderdate_orderId
on ordDemo(orderdate DESC,OrderId ASC)
GO
--running the same SELECT query we ran earlier to see
--behavior after covering Non-Clustered Index created
--with two fields
SELECT OrderDate,OrderID FROM ordDemo
WHERE OrderDate='2011-11-28 20:29:00.000'
GO