I have two stored procedures. In one of the procedures, the code uses the following hint: option(Optimize for Unknown). The other stored procedure uses this hint: option(Optimize for (@id Unknown). @id is a parameter that is being passed to the stored proc. What is the difference between these two hints?

CodePudding user response：

From the docs: (my bold)

OPTIMIZE FOR UNKNOWN

Instructs the Query Optimizer to use the average selectivity of the predicate across all column values instead of using the runtime parameter value when the query is compiled and optimized.

If you use OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN in the same query hint, the Query Optimizer will use the literal_constant specified for a specific value. The Query Optimizer will use UNKNOWN for the rest of the variable values. The values are used only during query optimization, and not during query execution.

So OPTIMIZE FOR UNKNOWN will apply OPTIMIZE FOR @variable_name UNKNOWN to each variable that is not already specified with OPTIMIZE FOR