USE AdventureWorks2008R2
GO

IF ( OBJECT_ID('dbo.TempPurchaseOrderHeader' ) is not NULL)
   DROP TABLE dbo.TempPurchaseOrderHeader
GO

-- Create a table from the contents of PurchaseOrderHeader 
SELECT * INTO TempPurchaseOrderHeader  FROM 
       Purchasing.PurchaseOrderHeader

-- Create a clustered index on the PurchaseOrderID 
-- and also a non-clustered index 

CREATE CLUSTERED INDEX  [TPOH_ClusteredIndex] ON 
         TempPurchaseOrderHeader      (  PurchaseOrderID)

CREATE NONCLUSTERED INDEX  [TPOH_NCLIndex] ON 
         TempPurchaseOrderHeader       ( VendorID)

-- Run the following query - does a CLUSTERED Index Scan
-- Why doesn't it use the non-clustered index and a seek?
  
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT  PurchaseOrderID, ShipMethodID, OrderDate, TotalDue
      FROM TempPurchaseOrderHeader WHERE VendorID = 1492


-- Non-clustered index takes about 73% of the cost!!!
-- Why? SQL Server must perform ""Key Lookup"" to retrieve
-- non-indexed columnns (shipmethodid, OrderDate, TotalDue, etc.)
     
SELECT  PurchaseOrderID, ShipMethodID, OrderDate, TotalDue  
       FROM TempPurchaseOrderHeader 
            WITH (INDEX ( [TPOH_NCLIndex]))
         WHERE VendorID = 1492 


-- now create a covering index!
           
CREATE NONCLUSTERED INDEX [TPOH_NCLIndex_Covering]    on 
          TempPurchaseOrderHeader   (VendorID)
  INCLUDE   (PurchaseOrderID, ShipMethodID,  OrderDate, TotalDue)
  
-- clustered index takes 26% of the batch

   SELECT  PurchaseOrderID, ShipMethodID, OrderDate, TotalDue 
       FROM TempPurchaseOrderHeader WITH (INDEX (0))
         WHERE VendorID = 1492
             
 -- non-clustered index takes 71% of the batch
 
  
   SELECT  PurchaseOrderID, ShipMethodID, OrderDate, TotalDue
       FROM TempPurchaseOrderHeader WITH (INDEX ( [TPOH_NCLIndex]))
            WHERE VendorID = 1492 

-- covering index takes 2% of the batch
   
   SELECT  PurchaseOrderID, ShipMethodID, OrderDate, TotalDue
        FROM TempPurchaseOrderHeader 
         WITH (INDEX ( [TPOH_NCLIndex_Covering]   ))
         WHERE VendorID = 1492