Export (0) Print
Expand All

Auto-Parameterization

SQL Server 2000

Auto-Parameterization

  New Information - SQL Server 2000 SP3.

In Microsoft® SQL Server™ 2000, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, unused execution plans.

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

If an SQL statement is executed without parameters, SQL Server 2000 parameterizes the statement internally to increase the possibility of matching it against an existing execution plan.

Consider this statement:

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

The value 1 at the end of the statement can be specified as a parameter. The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Because of this auto-parameterization, SQL Server 2000 recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

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

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

When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be auto-parameterized. To increase the ability of the relational engine to match complex SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers. For more information, see Parameters and Execution Plan Reuse.

Show:
© 2014 Microsoft