There are two different parameterization options that one can use in SQL Server. Simple parameterization and Forced parameterization. Let’s discuss each a little more in detail.
Simple Parameterization
When you execute a SQL statement without parameters, SQL Server internally will add parameters where needed so that it can try to reuse a cached execution plan. For example, if you look at the execution plan of the following statement you will see that SQL Server changes the WHERE value to a parameter (@1):
SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 11
SQL Server builds this execution plan as if a parameter was the input instead of the number 11. Because of this parameterization, the following two statements show an example of SQL Server reusing the same execution plan even though the data results are different:
SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 11 SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 207
This is the default behavior for Simple parameterization, however, it only applies to a small class of queries. If you want all your queries parameterized, you will need to enable the option, Forced parameterization.
Forced Parameterization
Forced parameterization is when the database engine parameterizes any literal value that appears in a SELECT, UPDATE, INSERT, or DELETE statement submitted in any form, but there are a few exceptions. Refer to this article for a list of these exceptions.
Some applications use queries that pass in literals as opposed to stored procedures that pass in parameters. For these type of applications you may want to experiment with enabling Forced parameterization to see if it has a positive effect on the workload by reducing query compilations.
Running the following query in Simple parameterization produces the following execution plan where the WHERE clause is not parameterized:
