/*
Documentation from MS help system

Understanding indexes: MS help system:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8de797d6-19da-44e8-9d24-c2689f42752b.htm

Index basics:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b7d6b323-728d-4763-a987-92e6292f6f7a.htm

Heap:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72f63db6-7306-4c43-a73d-7eaa4ffe1f82.htm

Designing indexes
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/cbb5bf99-6038-4a59-ada8-3886ef00454f.htm

General Index Design Guidelines
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e347ab5f-45a6-4ba8-898a-f83b4ccda028.htm

Clustered Index Structures 
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/26b28045-c3c2-465a-b564-bf2189e93fdc.htm

Covering indexes
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d198648d-fea5-416d-9f30-f9d4aebbf4ec.htm

Unique indexes
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/56b5982e-cb94-46c0-8fbb-772fc275354a.htm

Graphical Execution Plan Icons 
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/17d5daa1-8f14-46e2-9cea-0ed520217d1e.htm
*/

--For illustration, I'll make two copies of the Northwind.dbo.Orders table
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM Orders

/*
I'm ordering them by Freight just so the data won't be in order of the Primary key

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
INTO TempOrders1
FROM Orders
ORDER BY Freight

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
INTO TempOrders2
FROM Orders
ORDER BY Freight

DROP TABLE TempOrders1
DROP TABLE TempOrders2
*/
--The new table will require a Table Scan rather than an Index Scan
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM Orders
ORDER BY OrderID

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
ORDER BY OrderID

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
ORDER BY OrderID

--Add a primary key constraint to an existing attribute
ALTER TABLE TempOrders1
ADD CONSTRAINT ConstraintOrderID_PK PRIMARY KEY (OrderID)

ALTER TABLE TempOrders
DROP CONSTRAINT ConstraintOrderID_PK

--What if we only want a subset of rows?
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
WHERE CustomerID = 'VINET'

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
WHERE CustomerID = 'VINET'

/*
As we can see, the index scan is still faster than table scan
We can speed the query up by creating an index on CustomerID
*/

/*We can't create a clustered index because a table can only have one...*/

CREATE INDEX TempOrders1_Index 
    ON TempOrders1 (CustomerID)

DROP INDEX TempOrders.TempOrders_Index

--In this case, the optimizer a join of two seeks
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
WHERE CustomerID = 'VINET'

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
WHERE CustomerID = 'VINET'

/*
What if we modify the query just a bit...
TempOrders1 required a "Filter" or a scan of the intermediate results.
What can we do to tune this?
Note: in this case, CustomerID is really a key (a FK). A covering index allows
us to add non-key attributes to the index...
Create another index on both columns, or create a covering non-clustered index

*/

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
WHERE CustomerID = 'VINET' AND 
      Freight > 10

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
WHERE CustomerID = 'VINET' AND 
      Freight > 10

--We drop the index and recreate it with an INCLUDE clause
DROP INDEX TempOrders1.TempOrders1_Index

CREATE INDEX TempOrders1_Index 
    ON TempOrders1 (CustomerID)
INCLUDE (Freight)

--Which as we can see, eliminated the need for the filter operation...
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
WHERE CustomerID = 'VINET' AND 
      Freight > 10

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
WHERE CustomerID = 'VINET' AND 
      Freight > 10

/*
Normally a covering index includes all the columns referenced in 
the WHERE and SELECT clauses. But this isn't necessarily so. It
depends on the complexity and use of the query.
*/

DROP INDEX TempOrders1.TempOrders1_Index

CREATE INDEX TempOrders1_Index 
    ON TempOrders1 (CustomerID)
INCLUDE (OrderID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry)

/*
As we can see in the comparison, including all the columns in the
INCLUDE clause "covering" the query, increased the speed of the query, 
but in general, indexes should be as "narrow" as possible.
A good dba will only cover as much as is needed.
*/
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders1
WHERE CustomerID = 'VINET' AND 
      Freight > 10

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDAte, 
       ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
       ShipCity, ShipRegion, ShipPostalCode, ShipCountry 
FROM TempOrders2
WHERE CustomerID = 'VINET' AND 
      Freight > 10


/*
A rule of thumb is you should always consider creating an index on FKs.
CustomerID, EmployeeID, and ShipVia are FKs in this table.
*/

--Does an index always out perform a table scan?
SELECT *
FROM Customers

SELECT *
INTO Customers1
FROM Customers

SELECT *
FROM Customers

SELECT *
FROM Customers1

--But then it can sometimes make a BIG difference   
SELECT COUNT(*) 
FROM Customers
GROUP BY City

SELECT COUNT(*) 
FROM Customers1
GROUP BY City

DROP TABLE Customers1