-- Chapter 1: Introduction to Query Optimization

SELECT DISTINCT(City) FROM Person.Address
-- Listing 1-1.

SET SHOWPLAN_XML ON
GO
SELECT DISTINCT(City) FROM Person.Address
GO
SET SHOWPLAN_XML OFF
-- Listing 1-2.

SET SHOWPLAN_TEXT ON
GO
SELECT DISTINCT(City) FROM Person.Address
GO
SET SHOWPLAN_TEXT OFF
GO
-- Listing 1-4.

SELECT query_plan FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE session_id = 135
-- Listing 1-6.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
-- Listing 1-7.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
OPTION (FORCE ORDER)
-- Listing 1-8.


-- Chapter 2: The Execution Engine

SELECT * FROM DatabaseLog
-- Listing 2-1.

SELECT * FROM Person.Address
-- Listing 2-2.

SELECT * FROM Person.Address
ORDER BY AddressID
-- Listing 2-3.

SELECT AddressID, City, StateProvinceID FROM Person.Address
-- Listing 2-4.

SELECT AddressID, City, StateProvinceID FROM Person.Address
WHERE AddressID = 12037
-- Listing 2-5.

SELECT AddressID, StateProvinceID FROM Person.Address
WHERE StateProvinceID = 32
-- Listing 2-6.

SELECT AddressID, City, StateProvinceID, ModifiedDate
FROM Person.Address
WHERE StateProvinceID = 32
-- Listing 2-7.

SET SHOWPLAN_TEXT ON
GO
SELECT AddressID, City, StateProvinceID, ModifiedDate
FROM Person.Address
WHERE StateProvinceID = 32
GO
SET SHOWPLAN_TEXT OFF
GO
-- Listing 2-8.

SELECT AddressID, City, StateProvinceID, ModifiedDate
FROM Person.Address
WHERE StateProvinceID = 20
-- Listing 2-11.

CREATE INDEX IX_Object ON DatabaseLog(Object)
-- Listing 2-12.

SELECT * FROM DatabaseLog
WHERE Object = 'City'
-- Listing 2-13.

DROP INDEX DatabaseLog.IX_Object
-- Listing 2-14.

SELECT AVG(ListPrice) FROM Production.Product
-- Listing 2-15.

SET SHOWPLAN_TEXT ON
GO
SELECT AVG(ListPrice) FROM Production.Product
GO
SET SHOWPLAN_TEXT OFF
GO
-- Listing 2-16.

SELECT ProductLine, COUNT(*) FROM Production.Product
GROUP BY ProductLine
-- Listing 2-17.

SELECT SalesOrderID, SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
-- Listing 2-18.

SELECT ContactID, COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY ContactID
-- Listing 2-19.

CREATE INDEX IX_ContactID ON Sales.SalesOrderHeader(ContactID)
-- Listing 2-20.

DROP INDEX Sales.SalesOrderHeader.IX_ContactID
-- Listing 2-21.

SELECT ContactID, COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY ContactID
-- Listing 2-22.

SELECT DISTINCT(Title)
FROM HumanResources.Employee
SELECT Title
FROM HumanResources.Employee
GROUP BY Title
-- Listing 2-23.

CREATE INDEX IX_Title ON HumanResources.Employee(Title)
-- Listing 2-24.

DROP INDEX HumanResources.Employee.IX_Title
-- Listing 2-25.

SELECT DISTINCT(ContactID)
FROM Sales.SalesOrderHeader
SELECT ContactID
FROM Sales.SalesOrderHeader
GROUP BY ContactID
-- Listing 2-26.

SELECT e.EmployeeID
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s
ON e.EmployeeID = s.SalesPersonID
-- Listing 2-27.

SELECT e.EmployeeID
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS s
ON e.EmployeeID = s.SalesPersonID
WHERE TerritoryID = 1
-- Listing 2-28.

SELECT Name
FROM Sales.Store AS S
JOIN Sales.Customer AS C
ON S.CustomerID = C.CustomerID
WHERE C.CustomerType = N'S'
-- Listing 2-29.

SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
-- Listing 2-30.

SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
OPTION (MERGE JOIN)
-- Listing 2-31.

SELECT pv.ProductID, v.VendorID, v.Name
FROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v
ON (pv.VendorID = v.VendorID)
WHERE StandardPrice > $10
-- Listing 2-32.

SELECT I.CustomerID, C.FirstName, C.LastName, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA
ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A
ON A.AddressID = CA.AddressID
ORDER BY I.CustomerID
-- Listing 2-33.

SELECT I.CustomerID, C.FirstName, C.LastName, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA
ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A
ON A.AddressID = CA.AddressID
ORDER BY I.CustomerID
OPTION (MAXDOP 1)
-- Listing 2-34.

sp_configure 'cost threshold for parallelism', 6
GO
RECONFIGURE
GO
-- Listing 2-35.

sp_configure 'cost threshold for parallelism', 5
GO
RECONFIGURE
GO
-- Listing 2-36.


-- Chapter 3: Statistics and Cost Estimation

SELECT * FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderDetail')
-- Listing 3-1.

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', UnitPrice)
-- Listing 3-3.

SELECT * FROM Sales.SalesOrderDetail
WHERE UnitPrice = 35
-- Listing 3-4.

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)
-- Listing 3-6.

SELECT ProductID FROM Sales.SalesOrderDetail
GROUP BY ProductID
-- Listing 3-8.

DECLARE @ProductID int
SET @ProductID = 921
SELECT ProductID FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
-- Listing 3-9.

DECLARE @pid int = 897
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID < @pid
-- Listing 3-10.

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)
-- Listing 3-11.

SELECT ProductID, COUNT(*) AS Total
FROM Sales.SalesOrderDetail
WHERE ProductID BETWEEN 827 AND 831
GROUP BY ProductID
-- Listing 3-13.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 831
-- Listing 3-15.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID < 714
-- Listing 3-17.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 870 AND OrderQty = 1
-- Listing 3-18.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 870 OR OrderQty = 1
-- Listing 3-19.

SELECT * INTO dbo.SalesOrderDetail
FROM sales.SalesOrderDetail
-- Listing 3-20.

SELECT name, auto_created, stats_date(object_id, stats_id) AS update_date
FROM sys.stats
WHERE object_id = object_id('dbo.SalesOrderDetail')
-- Listing 3-21.

SELECT * FROM dbo.SalesOrderDetail
WHERE SalesOrderID = 43670 AND OrderQty = 1
-- Listing 3-22.

CREATE INDEX IX_ProductID ON dbo.SalesOrderDetail(ProductID)
-- Listing 3-23.

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, COLUMNS
-- Listing 3-25.

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, INDEX
-- Listing 3-27.

UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN
UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN, ALL
-- Listing 3-28.

ALTER INDEX ix_ProductID ON dbo.SalesOrderDetail REBUILD
-- Listing 3-29.

ALTER INDEX ix_ProductID on dbo.SalesOrderDetail REORGANIZE
-- Listing 3-30.

DROP TABLE dbo.SalesOrderDetail
-- Listing 3-31.

SELECT * FROM Sales.SalesOrderDetail
WHERE OrderQty * UnitPrice > 10000
-- Listing 3-32.

ALTER TABLE Sales.SalesOrderDetail
ADD cc AS OrderQty * UnitPrice
-- Listing 3-33.

SELECT * FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderDetail')
-- Listing 3-34.

DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', _WA_Sys_00000013_2645B050)
-- Listing 3-35.

SELECT * FROM Sales.SalesOrderDetail
WHERE UnitPrice * OrderQty > 10000
-- Listing 3-36.

ALTER TABLE Sales.SalesOrderDetail
DROP COLUMN cc
-- Listing 3-37.

SELECT * FROM Person.Address
WHERE City = 'Los Angeles'
-- Listing 3-38.

SELECT * FROM Person.Address
WHERE StateProvinceID = 9
-- Listing 3-39.

SELECT * FROM Person.Address
WHERE City = 'Los Angeles' AND StateProvinceID = 9
-- Listing 3-40.

CREATE STATISTICS california
ON Person.Address(City)
WHERE StateProvinceID = 9
-- Listing 3-41.

DBCC FREEPROCCACHE
GO
SELECT * FROM Person.Address
WHERE City = 'Los Angeles' AND StateProvinceID = 9
-- Listing 3-42.

DBCC SHOW_STATISTICS('Person.Address', california)
WITH STAT_HEADER
-- Listing 3-43.

DROP STATISTICS Person.Address.california
-- Listing 3-45.

SET STATISTICS PROFILE ON
GO
SELECT * FROM Sales.SalesOrderDetail
WHERE OrderQty * UnitPrice > 10000
GO
SET STATISTICS PROFILE OFF
GO
-- Listing 3-46.

SELECT * INTO dbo.Address
FROM Person.Address
-- Listing 3-48.

SELECT * FROM sys.partitions
WHERE object_id = object_id('dbo.Address')
-- Listing 3-49.

SELECT * FROM sys.dm_db_partition_stats
WHERE object_id = object_id('dbo.Address')
-- Listing 3-50.

SELECT * FROM dbo.Address
WHERE City = 'London'
-- Listing 3-51.

SELECT * FROM sys.stats
WHERE object_id = object_id('dbo.Address')
-- Listing 3-52.

DBCC SHOW_STATISTICS ('dbo.Address', _WA_Sys_00000004_46136164)
-- Listing 3-53.

UPDATE STATISTICS dbo.Address WITH ROWCOUNT = 1000000, PAGECOUNT = 100000
-- Listing 3-55.

DBCC FREEPROCCACHE
GO
SELECT * FROM dbo.Address
WHERE City = 'London'
-- Listing 3-56.

DROP TABLE dbo.Address
-- Listing 3-57.

SELECT * FROM Sales.SalesOrderDetail
WHERE LineTotal = 35
-- Listing 3-58.

SELECT in_row_data_page_count, row_count
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('Sales.SalesOrderDetail')
AND index_id = 1
-- Listing 3-59.


-- Chapter 4: Index Selection

SELECT ProductID, SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE ProductID = 771
-- Listing 4-1.

SELECT ProductID, SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE ABS(ProductID) = 771
-- Listing 4-3.

SELECT ProductID, SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE ProductID = 771 AND ABS(SalesOrderID) = 45233
-- Listing 4-5.

SELECT *
INTO dbo.SalesOrderDetail
FROM Sales.SalesOrderDetail
-- Listing 4-8.

SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = 897
-- Listing 4-9.

SELECT * FROM msdb..DTA_reports_query
-- Listing 4-10.

CREATE CLUSTERED INDEX [_dta_index_SalesOrderDetail_c_5_1915153868__K5]
ON [dbo].[SalesOrderDetail]
(
[ProductID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF) ON [PRIMARY]
-- Listing 4-12.

CREATE CLUSTERED INDEX cix_ProductID ON dbo.SalesOrderDetail(ProductID)
WITH STATISTICS_ONLY
-- Listing 4-13.

SELECT * FROM sys.indexes
WHERE object_id = object_id('dbo.SalesOrderDetail')
AND name = 'cix_ProductID'
-- Listing 4-14.

DROP INDEX dbo.SalesOrderDetail.cix_ProductID
-- Listing 4-16.

CREATE CLUSTERED INDEX cix_ProductID ON dbo.SalesOrderDetail(ProductID)
-- Listing 4-17.

DROP TABLE dbo.SalesOrderDetail
-- Listing 4-18.

SELECT *
INTO dbo.SalesOrderDetail
FROM sales.SalesOrderDetail
-- Listing 4-19.

SELECT * FROM dbo.SalesOrderDetail
WHERE SalesOrderID = 43670 AND SalesOrderDetailID > 112
-- Listing 4-20.

CREATE INDEX IX_ProductID ON dbo.SalesOrderDetail(ProductID)
-- Listing 4-21.

/*
Missing Index Details from SQLQuery1.sql – The Query Processor estimates that
implementing the following index could improve the query cost by 99.7137%.
*/
/*
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SalesOrderDetail] ([SalesOrderID], [SalesOrderDetailID])
GO
*/
-- Listing 4-23.

CREATE NONCLUSTERED INDEX IX_SalesOrderID_SalesOrderDetailID
ON [dbo].[SalesOrderDetail]([SalesOrderID], [SalesOrderDetailID])
-- Listing 4-24.

DROP TABLE dbo.SalesOrderDetail
-- Listing 4-25.

SELECT * INTO dbo.SalesOrderDetail
FROM Sales.SalesOrderDetail
CREATE NONCLUSTERED INDEX IX_ProductID ON dbo.SalesOrderDetail(ProductID)
-- Listing 4-26.

SELECT DB_NAME(database_id) as database_name,
OBJECT_NAME(s.object_id) as object_name, i.name, s.*
FROM sys.dm_db_index_usage_stats s join sys.indexes i
ON s.object_id = i.object_id AND s.index_id = i.index_id
and s.object_id = object_id('dbo.SalesOrderDetail')
-- Listing 4-27.

SELECT * FROM dbo.SalesOrderDetail
-- Listing 4-28.

SELECT ProductID FROM dbo.SalesOrderDetail
WHERE ProductID = 773
-- Listing 4-29.

SELECT * FROM dbo.SalesOrderDetail
WHERE ProductID = 773
-- Listing 4-30.

UPDATE dbo.SalesOrderDetail
SET ProductID = 666
WHERE ProductID = 927
-- Listing 4-31.

DROP TABLE dbo.SalesOrderDetail
-- Listing 4-33.


-- Chapter 5: The Optimization Process

SELECT * FROM sys.dm_exec_query_optimizer_info
-- Listing 5-1.

SELECT (SELECT occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'hints'
) * 100.0 / (SELECT occurrence
FROM
sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations'
)
-- Listing 5-2.

-- optimize these queries now
-- so they do not skew the collected results
GO
SELECT *
INTO after_query_optimizer_info
FROM sys.dm_exec_query_optimizer_info
GO
SELECT *
INTO before_query_optimizer_info
FROM sys.dm_exec_query_optimizer_info
GO
DROP TABLE before_query_optimizer_info
DROP TABLE after_query_optimizer_info
GO
-- real execution starts
GO
SELECT *
INTO before_query_optimizer_info
FROM sys.dm_exec_query_optimizer_info
GO
-- insert your query here
SELECT *
FROM Person.Address
-- keep this to force a new optimization
OPTION (RECOMPILE)
GO
SELECT *
INTO after_query_optimizer_info
FROM sys.dm_exec_query_optimizer_info
GO
SELECT a.counter,
(a.occurrence – b.occurrence) AS occurrence,
(a.occurrence * a.value – b.occurrence *
b.value) AS value
FROM before_query_optimizer_info b
JOIN after_query_optimizer_info a
ON b.counter = a.counter
WHERE b.occurrence <> a.occurrence
DROP TABLE before_query_optimizer_info
DROP TABLE after_query_optimizer_info
-- Listing 5-3.

SELECT * FROM sys.dm_exec_query_transformation_stats
-- Listing 5-4.

-- optimize these queries now
-- so they do not skew the collected results
GO
SELECT *
INTO before_query_transformation_stats
FROM sys.dm_exec_query_transformation_stats
GO
SELECT *
INTO after_query_transformation_stats
FROM sys.dm_exec_query_transformation_stats
GO
DROP TABLE after_query_transformation_stats
DROP TABLE before_query_transformation_stats
-- real execution starts
GO
SELECT *
INTO before_query_transformation_stats
FROM sys.dm_exec_query_transformation_stats
GO
-- insert your query here
SELECT * FROM dbo.DatabaseLog
-- keep this to force a new optimization
OPTION (RECOMPILE)
GO
SELECT *
INTO after_query_transformation_stats
FROM sys.dm_exec_query_transformation_stats
GO
SELECT a.name, (a.promised – b.promised) as promised
FROM before_query_transformation_stats b
JOIN after_query_transformation_stats a
ON b.name = a.name
WHERE b.succeeded <> a.succeeded
DROP TABLE before_query_transformation_stats
DROP TABLE after_query_transformation_stats
-- Listing 5-5.

SELECT * FROM dbo.DatabaseLog
-- Listing 5-6.

SELECT lname, fname FROM authors
-- Listing 5-7.

SELECT c.CustomerID, COUNT(*)
FROM Sales.Customer c JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
WHERE c.TerritoryID = 4
GROUP BY c.CustomerID
-- Listing 5-8.

SELECT c.CustomerID, COUNT(*)
FROM Sales.Customer c JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
-- Listing 5-9.

DBCC RULEOFF('GbAggBeforeJoin')
-- Listing 5-10.

DBCC TRACEON (3604)
DBCC SHOWONRULES
-- Listing 5-11.

DBCC SHOWOFFRULES
-- Listing 5-13.

DBCC RULEOFF('JNtoSM')
-- Listing 5-15.

DBCC RULEON('JNtoSM')
DBCC RULEON('GbAggBeforeJoin')
-- Listing 5-16.

DBCC SHOWOFFRULES
-- Listing 5-17.

DBCC FREEPROCCACHE
-- Listing 5-18.

SELECT FirstName, LastName, CustomerType
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
-- Listing 5-19.

SELECT FirstName, LastName --, CustomerType
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
-- Listing 5-23.

ALTER TABLE Sales.Individual NOCHECK CONSTRAINT FK_Individual_Customer_CustomerID
-- Listing 5-24.

ALTER TABLE Sales.Individual WITH CHECK CHECK CONSTRAINT FK_Individual_Customer_CustomerID
-- Listing 5-25.

SELECT * FROM HumanResources.Employee
WHERE VacationHours > 80
-- Listing 5-27.

SELECT * FROM HumanResources.Employee
WHERE VacationHours > 300
-- Listing 5-28.

ALTER TABLE HumanResources.Employee NOCHECK CONSTRAINT CK_Employee_VacationHours
-- Listing 5-29.

ALTER TABLE HumanResources.Employee WITH CHECK CHECK CONSTRAINT CK_Employee_VacationHours
-- Listing 5-30.

SELECT * FROM HumanResources.Employee
WHERE ManagerID > 10 AND ManagerID < 5
-- Listing 5-31.

SELECT * FROM dbo.DatabaseLog
-- Listing 5-32.

SELECT * FROM HumanResources.Employee
WHERE ManagerID = 12
-- Listing 5-34.

SELECT FirstName, LastName, CustomerType
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
-- Listing 5-36.

SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City,
SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
JOIN Person.StateProvince SP ON
SP.StateProvinceID = A.StateProvinceID
JOIN Person.CountryRegion CR ON
CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID
-- Listing 5-38.


-- Chapter 6: Additional Topics

INSERT INTO Person.CountryRegion (CountryRegionCode, Name) VALUES ('ZZ', 'NewCountry')
-- Listing 6-1.

DELETE FROM Person.CountryRegion
WHERE CountryRegionCode = 'ZZ'
-- Listing 6-2.

DELETE FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 61130
-- Listing 6-4.

DELETE FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID < 43740
-- Listing 6-5.

SELECT *
INTO dbo.Product
FROM Production.Product
-- Listing 6-6.

UPDATE dbo.Product
SET ListPrice = ListPrice * 1.2
-- Listing 6-7.

CREATE CLUSTERED INDEX cix ON dbo.Product(ListPrice)
-- Listing 6-8.

DROP TABLE dbo.Product
-- Listing 6-9.

USE AdventureWorksDW
GO
SELECT *
FROM dbo.FactInternetSales AS f
JOIN dbo.DimProduct AS p ON f.ProductKey = p.ProductKey
JOIN dbo.DimCustomer AS c ON f.CustomerKey = c.CustomerKey
WHERE p.ListPrice > 50 AND c.Gender = 'M'
-- Listing 6-10.

CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @pid
-- Listing 6-11.

EXEC test @pid = 897
-- Listing 6-12.

SET STATISTICS IO ON
GO
EXEC test @pid = 870
GO
-- Listing 6-13.

DBCC FREEPROCCACHE
GO
EXEC test @pid = 870
GO
-- Listing 6-15.

ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @pid
OPTION (OPTIMIZE FOR (@pid = 897))
-- Listing 6-16.

EXEC test @pid = 870
-- Listing 6-17.

ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @pid
OPTION (RECOMPILE)
-- Listing 6-19.

EXEC test @pid = 897
-- Listing 6-20.

ALTER PROCEDURE test (@pid int)
AS
DECLARE @p int = @pid
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @p
-- Listing 6-22.

ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @pid
OPTION (OPTIMIZE FOR UNKNOWN)
-- Listing 6-23.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 897
-- Listing 6-24.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 870
-- Listing 6-25.

SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43669
-- Listing 6-26.

SELECT text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%SalesOrderID%'
-- Listing 6-27.

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
-- Listing 6-29.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 897
-- Listing 6-30.

SELECT text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%Sales%'
-- Listing 6-31.

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE
-- Listing 6-33.


-- Chapter 7: Hints

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
-- Listing 7-1.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
OPTION (LOOP JOIN, MERGE JOIN)
-- Listing 7-2.

SELECT AddressID, City, StateProvinceID, ModifiedDate FROM Person.Address
WHERE City = 'Santa Fe'
OPTION (MERGE JOIN)
-- Listing 7-3.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID > I.ContactID
WHERE C.ContactID > 19974
OPTION (MERGE JOIN)
-- Listing 7-4.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
-- Listing 7-6.

SELECT FirstName, LastName
FROM Person.Contact AS C
INNER HASH JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
INNER LOOP JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
-- Listing 7-7.

SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
-- Listing 7-9.

SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
OPTION (HASH GROUP)
-- Listing 7-10.

SELECT COUNT(*)
FROM Sales.SalesOrderDetail
OPTION (HASH GROUP)
-- Listing 7-11.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
-- Listing 7-12.

SELECT FirstName, LastName
FROM Person.Contact AS C, Sales.Individual AS I, Sales.Customer AS Cu
WHERE I.CustomerID = Cu.CustomerID
AND C.ContactID = I.ContactID
AND Cu.CustomerType = 'I'
OPTION (FORCE ORDER)
-- Listing 7-13.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
OPTION (FORCE ORDER)
-- Listing 7-14.

SELECT FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
ON C.ContactID = I.ContactID
WHERE Cu.CustomerType = 'I'
OPTION (FORCE ORDER)
-- Listing 7-15.

SELECT c.CustomerID, COUNT(*)
FROM Sales.Customer c JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
-- Listing 7-16.

SELECT c.CustomerID, COUNT(*)
FROM Sales.Customer c JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
OPTION (FORCE ORDER)
-- Listing 7-17.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 897
-- Listing 7-18.

SELECT * FROM Sales.SalesOrderDetail
WITH (INDEX(0))
WHERE ProductID = 897
-- Listing 7-19.

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 870
-- Listing 7-20.

SELECT * FROM Sales.SalesOrderDetail
WITH (INDEX(IX_SalesOrderDetail_ProductID))
WHERE ProductID = 870
-- Listing 7-21.

SELECT * FROM Sales.SalesOrderDetail
WITH (FORCESEEK)
WHERE ProductID = 870
-- Listing 7-22.

SELECT * FROM Sales.SalesOrderDetail
WITH (INDEX(IX_SalesOrderDetail_ProductID), FORCESEEK)
WHERE ProductID = 870
-- Listing 7-23.

SELECT * FROM Sales.SalesOrderDetail
WITH (FORCESEEK)
WHERE OrderQty = 1
-- Listing 7-24.

SELECT * FROM Sales.SalesOrderDetail
ORDER BY ProductID
-- Listing 7-25.

SELECT * FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (FAST 20)
-- Listing 7-26.

CREATE VIEW v_test WITH SCHEMABINDING AS
SELECT SalesOrderID, COUNT_BIG(*) as cnt
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
GO
CREATE UNIQUE CLUSTERED INDEX ix_test ON v_test(SalesOrderID);
-- Listing 7-27.

SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
-- Listing 7-28.

SELECT SalesOrderID, COUNT(*)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
OPTION (EXPAND VIEWS)
-- Listing 7-29.

DROP VIEW v_test
-- Listing 7-30.

CREATE PROCEDURE test
AS
SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
-- Listing 7-31.

EXEC test
-- Listing 7-32.

EXEC sp_create_plan_guide
@name = N'plan_guide_test',
@stmt = N'SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID',
@type = N'OBJECT',
@module_or_batch = N'test',
@params = NULL,
@hints = N'OPTION (LOOP JOIN)';
-- Listing 7-33.

EXEC sp_control_plan_guide N'DISABLE', N'plan_guide_test';
-- Listing 7-34.

EXEC sp_control_plan_guide N'ENABLE', N'plan_guide_test';
-- Listing 7-35.

EXEC sp_control_plan_guide N'DROP', N'plan_guide_test';
DROP PROCEDURE test
-- Listing 7-36.

SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
-- Listing 7-37.

SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
OPTION (LOOP JOIN)
-- Listing 7-38.

SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> …
</ShowPlanXML>')
-- Listing 7-39.

EXEC sp_create_plan_guide
@name = N'plan_guide_test',
@stmt = N'SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID',
@type = N'OBJECT',
@module_or_batch = N'test',
@params = NULL,
@hints = N'OPTION (USE PLAN N''<?xml version="1.0" encoding="utf-16"?> …
-- Listing 7-40.