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 two queries is the value stored for the comparison against the CategoryID column. It would be good if Microsoft® SQL Server™ version 7.0 recognized that the statements generate essentially the same plan and reused the plans.

Separating constants from the SQL statement by using parameters helps the relational engine recognize duplicate plans. There are two ways to use parameters:

• 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 particular method is best suited 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 OLE DB Provider for SQL Server and the SQL Server ODBC Driver that ship with SQL Server 7.0 both use sp_executesql to send statements to SQL Server 7.0 when parameter markers are used in applications.

See Also