Database Reference
In-Depth Information
JOIN Person.BusinessEntityAddress AS bea
ON e.BusinessEntityID = bea.BusinessEntityID
JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
JOIN Person.EmailAddress AS ea
ON p.BusinessEntityID = ea.BusinessEntityID
WHERE ea.EmailAddress LIKE 'david%'
AND sp.StateProvinceCode = @0
Because of its restrictions, forced parameterization was unable to substitute anything for the string
'david%'
,
but it was able to for the string
'WA'
. Worth noting is that the variable was declared as a full 8,000-length
VARCHAR
instead of the three-character
NCHAR
like the actual column in the
Person.StateProvince
table. Although you have a
parameter, it might lead to implicit data conversions that could prevent the use of an index.
Before you start using forced parameterization, the following list of restrictions may give you information to help
you decide whether forced parameterization will work in your database. (This is a partial list; for the complete list,
please consult Books Online.)
•
INSERT ... EXECUTE
queries
Statements inside procedures, triggers, and user-defined functions since they already have
•
execution plans
•
Client-side prepared statements (you'll find more detail on these later in this chapter)
•
Queries with the query hint
RECOMPILE
Pattern and escape clause arguments used in a
LIKE
statement (as shown earlier)
This gives you an idea of the types of restrictions placed on forced parameterization. Forced parameterization is
really going to be potentially helpful only if you are suffering from large amounts of compiles and recompiles because
of ad hoc queries. Any other load won't benefit from the use of forced parameterization.
Before continuing, change the database back to
SIMPLE PARAMETERIZATION
.
•
ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION SIMPLE;
Plan Reusability of a Prepared Workload
Defining queries as a prepared workload allows the variable parts of the queries to be explicitly parameterized. This
enables SQL Server to generate a query plan that is not tied to the variable parts of the query, and it keeps the variable
parts separate in an execution context. As you saw in the previous section, SQL Server supports three techniques to
submit a prepared workload.
•
Stored procedures
sp_executesql
•
•
Prepare/execute model
In the sections that follow, I cover each of these techniques in more depth and point out where it's possible for
parameterized execution plans to cause problems.
