Forced parameterization

If an application tends to generate many ad hoc queries, and there is no way to modify the application to parameterize the queries, the Parameterization database option can be changed to Forced. When Forced Parameterization is turned on, SQL Server will replace ALL literal values in ALL ad hoc queries with parameter markers. Take the example of the following query executed in the AdventureWorks sample database:

SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE PersonType = N'EM' AND BusinessEntityID IN (5, 7, 13, 17, 19);

This query would be automatically parameterized under Forced Parameterization, as follows:

(@1 nchar(2), @2 int, @3 int, @4 int, @5 int, @6 int)  SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE PersonType = @1 AND BusinessEntityID IN (@2, @3, @4, @5, @6);

This has the benefit of increasing the reusability of all ad hoc queries, but there are some risks to parameterizing all literal values in all queries, which will be discussed later in section The importance of parameters.