Parameters and Execution Plan Reuse

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.

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

SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 1

SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 4

The only difference between the execution plans for these queries is the value stored for the comparison against the CategoryID column. While the goal is for SQL Server 2000 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 Northwind.dbo.Products WHERE CategoryID = @Parm',
    N'@Parm INT',
    @MyIntParm
  

  This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically. For more information, see Building Statements at Run Time.

• 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, in an ODBC application:
  • Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    
    
  • Place the integer value in the variable.
    
    
  • Execute the statement, specifying the parameter marker (?):

    SQLExecDirect(hstmt, 
      "SELECT * FROM Northwind.dbo.Products WHERE CategoryID = ?",
      SQL_NTS);
    

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

See Also

sp_executesql

Using Parameters

Command Parameters

Using Statement Parameters