Reutilização de parâmetros e plano de execução

O uso de parâmetros, inclusive de marcadores de parâmetro em aplicativos de ADO, OLE DB e ODBC, pode aumentar a reutilização de planos de execução.

Observação sobre segurança
O uso de parâmetros ou marcadores de parâmetro para manter valores digitados pelo usuário final é mais seguro que a concatenação dos valores em uma cadeia de caracteres executada posteriormente usando um método API de acesso a dados, a instrução EXECUTE ou o procedimento armazenado de sp_executesql.

A única diferença entre as duas instruções SELECT a seguir são os valores comparados na cláusula WHERE:

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 1;

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 4;

A única diferença entre os planos de execução dessas consultas é o valor armazenado para a comparação com a coluna ProductSubcategoryID. Quando a meta for para o SQL Server sempre reconhecer que as instruções geram essencialmente o mesmo plano e reutilizam os planos, às vezes, o SQL Server não detecta isso em instruções SQL complexas.

A separação de constantes da instrução SQL usando parâmetros ajuda o mecanismo relacional a reconhecer planos duplicados. Você pode usar parâmetros dos seguintes modos:

• No Transact-SQL, use sp_executesql:

  DECLARE @MyIntParm INT
  SET @MyIntParm = 1
  EXEC sp_executesql
    N'SELECT * 
    FROM AdventureWorks2008R2.Production.Product 
    WHERE ProductSubcategoryID = @Parm',
    N'@Parm INT',
    @MyIntParm
  

  Esse método é recomendado para scripts Transact-SQL, procedimentos armazenados ou gatilhos que geram instruções SQL dinamicamente.

• ADO, OLE DB e ODBC usam marcadores de parâmetro. Marcadores de parâmetro são pontos de interrogação (?) que substituem uma constante em uma instrução SQL e são associados a uma variável de programa. Por exemplo, você faria o seguinte em um aplicativo de ODBC:

  • Use o SQLBindParameter para associar uma variável de inteiro ao primeiro marcador de parâmetro em uma instrução SQL.

  • Coloque o valor inteiro na variável.

  • Execute a instrução, especificando o marcador de parâmetro (?):

    SQLExecDirect(hstmt, 
      "SELECT * 
      FROM AdventureWorks2008R2.Production.Product 
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
    

    O SQL Server Native Client OLE DB Provider e o driver SQL Server Native Client ODBC incluídos com o SQL Server usam sp_executesql para enviar instruções ao SQL Server quando os marcadores de parâmetro são usados em aplicativos.

• Para criar procedimentos armazenados que usam parâmetros por design.

Se você não criar parâmetros explicitamente com o design de seus aplicativos, também poderá contar com o otimizador de consulta do SQL Server para parametrizar determinadas consultas automaticamente usando o comportamento padrão da Parametrização simples. Como alternativa, você pode forçar o otimizador de consulta para considerar a parametrização de todas as consultas no banco de dados, definindo a opção PARAMETERIZATION da instrução ALTER DATABASE como FORCED. Para obter mais informações, consulte Parametrização forçada.

Quando a Parametrização Forçada está habilitada, a Parametrização Simples ainda pode acontecer. Por exemplo, a consulta a seguir não pode ser parametrizada de acordo com as regras de parametrização forçada:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

Porém, ela pode ser parametrizada de acordo com as regras de parametrização simples. Quando se tenta usar a parametrização forçada, mas ela falha, há uma tentativa subsequente de parametrização simples.