Simple parameterization

In order to minimize the impact of ad hoc queries, SQL Server will automatically parameterize some simple queries by default. This is called Simple parameterization and is the default setting of the parameterization database option. With parameterization set to Simple, SQL Server will automatically replace literal values in an ad hoc query with parameter markers in order to make the resulting query plan reusable. This works for some queries, but there is a very small class of queries that can be parameterized this way.

As an example, the query we introduced previously in the Parameterization section would not be automatically parameterized in simple mode because it is considered unsafe. This is because different PersonType values may yield a different number of rows, and thus require a different execution plan. However, the following query executed in the AdventureWorks sample database would qualify for simple automatic parameterization:

SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE BusinessEntityID = 5;

This query would not be cached as is. SQL Server would convert the literal value of 5 to a parameter marker, and it would look something like this in the cache:

(@1 tinyint) SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE BusinessEntityID = @1;