Guru's Guide to SQL Server Architecture and Internals, The

Auto-Parameterization

In order to increase the likelihood of plan reuse, SQL Server attempts to automatically parameterize ad hoc queries. By “auto-parameterize,” I mean that the server can replace a constant value in an ad hoc query with a parameter marker so that the plan can be reused with different values for the constant. Consider the following query, for example:

SELECT * FROM Orders WHERE OrderId=10248

In this query, the value 10248 is a constant. If the server were unable to auto-parameterize the query, a value other than 10248 would result in a separate execution plan being generated. Instead, the server is intelligent enough to replace 10248 with a parameter marker and supply 10248 as the value of the parameter for a given execution ...