Home » Microsoft » MB6-886 » What is the result?
You have the following Transact-SQL code:
DECLARE @t int;
SET @t = 9
SELECT * From SalesHeader
WHERE TerritoryID = @tOPTION (OPTIMIZE FOR (@t=7);
What is the result?
A. The optimizer optimizes the query using parameter sniffing.
B. The optimizer uses a parameter value of 7 when optimizing the query.
C. The optimizer uses a parameter value of 9 when optimizing the query.
D. An error occurs because different values were specified for @t
Correct Answer: B
Explanation/Reference:
The OPTIMIZE FOR query hint forces the optimizer to use a specific parameter value when optimizing the query. In this scenario, you specified OPTIMIZE FOR
(@t=7). Therefore, the optimizer will use a parameter value of 7 when optimizing the query.
The optimizer does not optimize the query using parameter sniffing. Parameter sniffing occurs when SQL Server detects the current parameter value during compilation or recompilation, and passes it to the query optimizer. The query optimizer can then use this value to generate better execution plans. Parameter sniffing occurs automatically. However, in this scenario, you specified the OPTIMIZE FOR hint, which causes the optimizer to use a specific parameter value when optimizing the query.
The optimizer does not use a parameter value of 9 when optimizing the query. In this scenario, you set the value of @t to 9, but you override the parameter value the optimizer will use by specifying the OPTIMIZE FOR hint.
An error does not occur because different values were specified for @t. Even though the current value of @t is 9, you can specify a different value for @t in the OPTIMIZE FOR hint.
