
USE AdventureWorks;
GO

-- turn on "Include Actual Execution Plan"
SELECT Name, SalesOrderNumber, OrderDate, TotalDue
FROM Sales.Store AS S
    JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID 
ORDER BY Name, OrderDate;
GO

-- not a particularly good hint
SELECT Name, SalesOrderNumber, OrderDate, TotalDue
FROM Sales.Store AS S
    JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID 
ORDER BY Name, OrderDate 
OPTION (MERGE JOIN);
GO

-- cached plan should optimize for CA
DECLARE @state_name CHAR(2)
SET @state_name = 'CA'
SELECT * FROM Person.Address AS A
  JOIN Person.StateProvince AS S 
  ON A.StateProvinceID = S.StateProvinceID
WHERE S.StateProvinceCode = @state_name
OPTION ( OPTIMIZE FOR (@state_name = 'CA') )
GO


-- create plan guide for our query hint
sp_create_plan_guide 
  @name = N'PortlandGuide', 
  @stmt = N'SELECT * FROM Person.Address WHERE City = @city_name',  
  @type = N'SQL',
  @module_or_batch = NULL, 
  @params = N'@city_name NVARCHAR(30)', 
  @hints = N'OPTION (OPTIMIZE FOR (@city_name = N''Portland''))'
GO
sp_control_plan_guide N'ENABLE', N'PortlandGuide'
GO

