
USE AdventureWorks
GO

-- The following is an example of a plan guide created for a simple SQL statement:
-- Adds a join hint

sp_create_plan_guide
@name = N'PlanGuide1',
@stmt = N'SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate 
BETWEEN ''1/1/2000'' AND ''1/1/2005''
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO

sp_control_plan_guide N'ENABLE', N'PlanGuide1'
go

-- now use it
SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate 
BETWEEN '1/1/2000' AND '1/1/2005'

sp_control_plan_guide N'DISABLE', N'PlanGuide1'
go

-- without it, different plan
SELECT COUNT(*) AS Total
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate 
BETWEEN '1/1/2000' AND '1/1/2005'
