Database Reference
In-Depth Information
But, if you have a query that is in any way complicated, you won't get simple parameterization.
SELECT ea.EmailAddress,
e.BirthDate,
a.City
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
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 = 'WA';
When you run this query, simple parameterization is not applied, as you can see in Figure 15-11 .
Figure 15-11. A more complicated query doesn't get parameterized
No prepared plans are visible in the output from sys.dm_exec_cached_plans . But if you use the previous script
to set PARAMETERIZATION to FORCED , clear the cache, and rerun the query, the output from sys.dmexeccachedplans
changes so that the output looks different, as shown in Figure 15-12 .
Figure 15-12. Forced parameterization changes the plan
Now a prepared plan is visible in the third row. However, only a single parameter was supplied, @0 varchar(8000) .
If you get the full text of the prepared plan out of sys.dm_exec_ querytext and format it, it looks like this:
(@0 varchar(8000))
SELECT ea.EmailAddress,
e.BirthDate,
a.City
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
Search WWH ::




Custom Search