SQL Server Index Basics - Part 1

SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
--Index
CREATE INDEX ix_1 ON sales.SalesOrderDetail (
SalesOrderID
,ProductID
,unitprice
)
--Query
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
GO
--Query with Index hint
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail WITH (INDEX ([PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]))
--IO Stats
SET STATISTICS IO ON
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail WITH (INDEX ([PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]))
SET STATISTICS IO OFF
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
WHERE ProductID = @ProductID
--Index
CREATE INDEX ix_2 ON sales.SalesOrderDetail (
ProductID
,unitprice
,SalesOrderID)
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
WHERE ProductID = @ProductID
--Covering Index
CREATE INDEX ix_3 ON sales.SalesOrderDetail (ProductID) INCLUDE (
unitprice
,SalesOrderID
)
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
WHERE ProductID = @ProductID
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail WITH (INDEX (ix_2))
WHERE ProductID = @ProductID
GO
DROP INDEX ix_2 on sales.SalesOrderDetail
GO
CREATE INDEX ix_3 ON sales.SalesOrderDetail (ProductID) INCLUDE (SalesOrderID)
WITH (DROP_EXISTING = ON)
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail WITH (INDEX(IX_3))
WHERE ProductID = @ProductID
GO
Drop index ix_1 on sales.SalesOrderDetail
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
WHERE ProductID = @ProductID
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail WITH (INDEX (IX_3))
WHERE ProductID = @ProductID
CREATE INDEX ix_3 ON sales.SalesOrderDetail (ProductID) INCLUDE (unitprice)
WITH (DROP_EXISTING = ON)
GO
DECLARE @ProductID INT = 776
SELECT SalesOrderID,ProductID,unitprice FROM sales.SalesOrderDetail
WHERE ProductID = @ProductID