
USE AdventureWorks
GO

-- these could be parameterized, they are very similar
-- but with "parameterization simple", they aren't autoparameterized
SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45639
GO

SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45640
GO

-- get query template
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45640',
@stmt OUTPUT, 
@params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
@stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)'

sp_control_plan_guide N'ENABLE', N'TemplateGuide1'
go

-- now its autoparameterized
-- note: no "plan guide" reference appears in the XML plan
-- but there ARE plan operations for
--  QUERY_STATS, FNGETSQL, FNGETQUERYPLAN
SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45640
GO

sp_control_plan_guide N'DROP', N'TemplateGuide1'
go

-- now its not
SELECT * 
FROM AdventureWorks.Sales.SalesOrderHeader h, 
     AdventureWorks.Sales.SalesOrderDetail d 
WHERE h.SalesOrderID = d.SalesOrderID AND h.SalesOrderID = 45640
GO

