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