Parameters and Execution Plan Reuse

The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Security Note
Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

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

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

The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. While the goal is for SQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL Server sometimes does not detect this in complex SQL statements.

Separating constants from the SQL statement by using parameters helps the relational engine recognize duplicate plans. You can use parameters in the following ways:

• In Transact-SQL, use sp_executesql:

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

  This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

• ADO, OLE DB, and ODBC use parameter markers. Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. For example, you would do the following in an ODBC application:

  • Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.

  • Put the integer value in the variable.

  • Execute the statement, specifying the parameter marker (?):

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

    The SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver included with SQL Server use sp_executesql to send statements to SQL Server when parameter markers are used in applications.

• To design stored procedures, which use parameters by design.

If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL Server query optimizer to automatically parameterize certain queries by using the default behavior of Simple Parameterization. Alternatively, you can force the query optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED. For more information, see Forced Parameterization.

When Forced Parameterization is enabled, Simple Parameterization can still occur. For example, the following query cannot be parameterized according to the rules of forced parameterization:

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

However, it can be parameterized according to simple parameterization rules. When forced parameterization is tried but fails, simple parameterization is still subsequently tried.