Databases Reference
In-Depth Information
Running the following two versions of our stored procedure will have equivalent
outcomes, and will produce the same execution plan. The first version uses local
variables, and the second one uses the new
OPTIMIZE
FOR
UNKNOWN
hint.
ALTER
PROCEDURE
test
(
@pid
int
)
AS
DECLARE
@p
int
=
@pid
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@p
Listing 6-22.
ALTER
PROCEDURE
test
(
@pid
int
)
AS
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@pid
OPTION
(
OPTIMIZE
FOR
UNKNOWN
)
Listing 6-23.
In this case, the Query Optimizer will create the plan using the Clustered Index Scan
shown previously.
Auto-parameterization
The Query Optimizer might decide to parameterize queries in those cases where the
value of a specific parameter does not impact the choice of an execution plan. That is, in
the cases where it does not matter which parameter value is used, the plan returned will
be the same.
This is a very conservative policy and SQL Server will only use it when it is safe to do
so, and the performance of the queries will not be negatively impacted. In this case, the
