Criar um guia de plano para consultas parametrizadas

Um guia de plano TEMPLATE corresponde a consultas autônomas que parametrizam com um formulário especificado.

O exemplo a seguir cria um guia de plano que corresponde a qualquer consulta parametrizada com um formulário especificado e direciona o SQL Server para forçar a parametrização da consulta. As duas consultas a seguir são sintaticamente equivalentes, mas diferem apenas em seus valores literais constantes.

SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45640;  

Aqui está o guia de plano na forma parametrizada da consulta:

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
                  ON h.SalesOrderID = d.SalesOrderID  
              WHERE h.SalesOrderID = @0',  
    @type = N'TEMPLATE',  
    @module_or_batch = NULL,  
    @params = N'@0 int',  
    @hints = N'OPTION(PARAMETERIZATION FORCED)';  

No exemplo anterior, o valor do @stmt parâmetro é a forma parametrizada da consulta. A única maneira confiável de obter esse valor para uso no sp_create_plan_guide é usar o procedimento armazenado do sistema sp_get_query_template . O script a seguir pode ser usado para obter a consulta parametrizada e, em seguida, criar um guia de plano sobre ela.

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d   
          ON h.SalesOrderID = d.SalesOrderID  
      WHERE h.SalesOrderID = 45639;',  
    @stmt OUTPUT,   
    @params OUTPUT  
EXEC sp_create_plan_guide N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

Você também pode usar guias de plano TEMPLATE junto com guias de plano SQL. Por exemplo, você pode criar um guia de plano TEMPLATE para garantir que uma classe de consultas seja parametrizada. Em seguida, você pode criar um guia de plano SQL na forma parametrizada dessa consulta.