-------------------------------------------------------
-- Estimated and Actual Execution Plans
------------------------------------------------------

-- This demo relies on AdventureWorks2012 DB 
-- Which can be found in http://msftdbprodsamples.codeplex.com/releases/view/93587

-- Open a new query window to the AdventureWorks2012 database

USE AdventureWorks2012;

-- Demo 1 - Display Estimated Execution Plan
--------------------------------------------
-- First time for query number 1 
-- Second time for query number 2 
-- Third time for both of them - which cost is cheaper and why ? 
-- * You can use CTRL-L as a shortcut to  Display Estimated Execution Plan

SELECT ProductID, Name, Color 
FROM Production.Product
WHERE Color IN ('Blue','Red')
ORDER BY Name;

SELECT ProductID, Name, Color 
FROM Production.Product
WHERE Color IN ('Blue','Red')
ORDER BY ProductID;

-- Demo 2 - Display Estimated Execution Plan
--------------------------------------------
-- First time for CREATE TABLE  
-- Second time for INSERT, is it possible ? 
-- Third time for both of them, again - is it possible ?  

CREATE TABLE SomeTable
( SomeTableID INT IDENTITY(1, 1) PRIMARY KEY,
  FullName varchar(35))

INSERT INTO SomeTable VALUES('Hello'),('There');
SELECT * FROM SomeTable;
DROP TABLE SomeTable;


-- Demo 3 - Display Estimated Execution Plan
--------------------------------------------

-- Review the following tables : 

SELECT * FROM [Production].[Product]

SELECT * FROM [Production].[ProductModel]

-- Display Estimated Execution Plan 
-- * First time for query number 1 
-- * Second time for query number 2 
-- * Now use the estimated execution plan to compare two queries, How do you 
--   explain that such different queries return the same plan?

SELECT p.ProductID, p.Name, p.Color, p.Size
FROM Production.Product AS p LEFT OUTER JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID 
WHERE pm.ProductModelID IS NOT NULL;

SELECT p.ProductID, p.Name, p.Color, p.Size
FROM Production.Product AS p
WHERE EXISTS(SELECT 1 FROM Production.ProductModel AS pm
             WHERE p.ProductModelID = pm.ProductModelID);

-- Demo 4 - Include Actual Execution Plan
-----------------------------------------

-- Execute the query and include Actual Execution Plan
-- * Shortcut for Include Actual Execution Plan : CTRL + M

SELECT ProductID, Name, Color 
FROM Production.Product
WHERE Color IN ('Blue','Red')
ORDER BY Name;

------------------------------------------
-- Save EP & Show EP as XML
------------------------------------------

-- Right-click on the plan and choose "Save Execution Plan As"
-- Right-click on the plan and choose "Show Execution Plan XML"


------------------------------
-- SET SHOWPLAN_TEXT ON / OFF
-----------------------------

-- * Causes Microsoft® SQL Server™ not to execute Transact-SQL statements. 
-- Instead, SQL Server returns detailed information about how the statements are executed.

-- Can be executed via SSMS or SQLCMD 
-- * In order to use SQLCMD from command prompt type - 
--   SQLCMD -S <server name>/<instance name) for example : 
--   sqlcmd -S WIN-48RSPI12UJH\INST01

USE AdventureWorks2012;

SET SHOWPLAN_TEXT ON;

SELECT *
FROM Production.Product 
WHERE ProductID = 905;

SET SHOWPLAN_TEXT OFF;

---------------------------------------
-- SET SHOWPLAN_ALL ON / OFF
---------------------------------------
-- Causes Microsoft SQL Server not to execute Transact-SQL statements. 
-- Instead, SQL Server returns detailed information about how the statements are executed 
-- and provides estimates of the resource requirements for the statements

-- Can be executed via SSMS or SQLCMD 
-- * In order to use SQLCMD from command prompt type - 
--   SQLCMD -S <server name>/<instance name) for example : 
--   sqlcmd -S WIN-48RSPI12UJH\INST01

USE AdventureWorks2012;

SET SHOWPLAN_ALL ON;

SELECT *
FROM Production.Product 
WHERE ProductID = 905;

SET SHOWPLAN_ALL OFF;

-------------------------------------------------------------------------------
-- Different Access Paths 
--------------------------------------------------------------------------------

USE AdventureWorks2012;

--------------------------------------------------------------------------------
-- Heap Table 
--------------------------------------------------------------------------------
-- ***Heap Table - A table without a clustered index and is sorted.
-- 1. Full Table Scan - Scans All rows in the table.
-- 2. Non Clustered Index Seek - MSQL Uses the b-tree structure of the index to seek directly to matching records.
-- 3. Non Clustered Index Scan - MSQL Reads the whole of the index looking for matches.


--RID (ROW ID) Lookup - A lookup into a heap table using a Row ID > 
--The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table

-- Row ID Example Using %%physloc%%--
SELECT %%physloc%% As RID from dbo.DatabaseLog;

SELECT databaselogid, %%physloc%% As RID from dbo.DatabaseLog;

SELECT databaselogid, %%physloc%% As RID from dbo.DatabaseLog
WHERE %%physloc%% = 0x1101000001000000;

SELECT * FROM sys.indexes WHERE object_id = object_id ('dbo.DatabaseLog')

-- 1. Full Table Scan 
---------------------

SELECT * FROM dbo.DatabaseLog;

SELECT * FROM dbo.DatabaseLog WHERE event = 'ALTER_TABLE' 

-- 2. Non Clustered Index Seek 
------------------------------------------
-- Retrieves selective rows from the table

SELECT * FROM dbo.DatabaseLog WHERE DatabaseLogID = 1;

--  3. Non Clustered Index Scan 
----------------------- ----------------
-- retrieves all the rows from the index

SELECT DatabaseLogID From DatabaseLog

--------------------------------------------------------------------------------
-- Clustered Index 
--------------------------------------------------------------------------------

-- 1. Clustered Index Scan - MSQL Reads the whole of the index looking for matches.
-- 2. Clustered Index Seek - MSQL Uses the b-tree structure of the index to seek directly to matching records.
-- 3. Non Clustered Index Seek
-- 4. Non Clustered Index Scan

SELECT * FROM sys.indexes WHERE object_id = object_id ('Person.ContactType')

-- 1. Clustered Index Scan
---------------------------------------
-- Retrieves all the rows from the table

SELECT * FROM Person.ContactType;

-- 2. Clustered Index Seek 
------------------------------------------
-- Retrieves selective rows from the table

SELECT * FROM Person.ContactType WHERE [ContactTypeID] = 12;

SELECT * FROM Person.ContactType WHERE [ContactTypeID] BETWEEN 10 AND 20;

-- 3. Non Clustered Index Seek
------------------------------------------
-- Retrieves selective rows from the table

SELECT * FROM Person.ContactType WHERE [Name] = 'some value';

--key lookup - A key lookup is an extra read which the query has to make in the absence of a suitable covering index. 

-- 4. Non Clustered Index Scan
----------------------------------------
-- Retrieves all the rows from the index

SELECT [Name] FROM Person.ContactType ;

--------------------------------------------------------------------------------
-- Sort Operations 
--------------------------------------------------------------------------------

SELECT * FROM sys.indexes WHERE object_id = object_id ('Production.ProductInventory')

-- Compare the different queries and note the costs : 

SELECT * 
FROM Production.ProductInventory
ORDER BY Shelf;

SELECT * 
FROM Production.ProductInventory
ORDER BY [ProductID] , [LocationID];

SELECT * 
FROM Production.ProductInventory
ORDER BY [LocationID] , [ProductID];