Simple Parameterization
In SQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.
.gif "Security note") Security Note |
|---|
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 a SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. This process is called simple parameterization. In SQL Server 2000, the process was referred to as auto-parameterization.
Consider this statement:
SELECT * FROM AdventureWorks2008R2.Production.Product
WHERE ProductSubcategoryID = 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 simple parameterization, SQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:
SELECT * FROM AdventureWorks2008R2.Production.Product
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2008R2.Production.Product
WHERE ProductSubcategoryID = 4;
When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be 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.
Note
When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL Server applies specific rules to calculate the type and precision of the expression results. However, these rules differ, depending on whether the query is parameterized or not. Therefore, similar expressions in queries can, in some cases, produce differing results. For more information, see int, bigint, smallint, and tinyint (Transact-SQL).
Under the default behavior of simple parameterization, SQL Server parameterizes a relatively small class of queries. However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations. For more information, see Forced Parameterization.
Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized. For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.