USE AdventureWorks
GO

-- 1 - Review the indexes
sp_help [Purchasing.PurchaseOrderHeader] 
GO

-- 2 - No index analysis
-- No index

-- 2a - DESC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate 
GO

-- 2b - ASC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate ASC
GO


-- 3 - DESC order
DROP INDEX [Purchasing].[PurchaseOrderHeader].[IX_PurchaseOrderHeader_OrderDate] 
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] 
ON [Purchasing].[PurchaseOrderHeader] 
( [OrderDate] DESC ) 
GO

-- 3a - DESC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate 
GO

-- 3b - ASC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate ASC
GO


-- 4 - ASC order
DROP INDEX [Purchasing].[PurchaseOrderHeader].[IX_PurchaseOrderHeader_OrderDate] 
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] 
ON [Purchasing].[PurchaseOrderHeader] 
( [OrderDate] ASC ) 
GO

-- 4a - DESC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate 
GO

-- 4b - ASC order
SELECT TOP 10 OrderDate
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate ASC
GO

-- 5 - Covering index example ASC, ASC
DROP INDEX [Purchasing].[PurchaseOrderHeader].[IX_PurchaseOrderHeader_OrderDate] 
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] 
ON [Purchasing].[PurchaseOrderHeader] 
( [OrderDate] ASC, [SubTotal] ASC ) 
GO

-- 5a - ASC, ASC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate ASC, SubTotal ASC
GO

-- 5b - DESC, DESC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate, SubTotal
GO

-- 5c - DESC, ASC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate, SubTotal DESC
GO

-- 6 - Covering index example ASC, DESC
DROP INDEX [Purchasing].[PurchaseOrderHeader].[IX_PurchaseOrderHeader_OrderDate] 
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] 
ON [Purchasing].[PurchaseOrderHeader] 
( [OrderDate] ASC, [SubTotal] DESC ) 
GO

-- 6a - ASC, ASC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate ASC, SubTotal ASC
GO

-- 6b - DESC, DESC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate, SubTotal
GO

-- 6c - DESC, ASC
SELECT TOP 10 OrderDate, SubTotal
FROM [Purchasing].[PurchaseOrderHeader] 
ORDER BY OrderDate, SubTotal DESC
GO


-- 7 - Final cleanup
DROP INDEX [Purchasing].[PurchaseOrderHeader].[IX_PurchaseOrderHeader_OrderDate] 
GO
