patternsqlMinor
Local Variables in Stored Procedures
Viewed 0 times
storedlocalproceduresvariables
Problem
I've read online that if you use local variables in stored procedures instead of the input variables then the stored procedure is optimized as if it was using the hint OPTIMIZE FOR UNKNOWN. How exactly does that happen? Also, what's a good practice in general: use input variables directly or create local variables and assign the input variables to them?
Solution
post it
From my blog post Yet Another Post About Local Variables
In a stored procedure (and even in ad hoc queries or within dynamic
SQL, like in the examples linked above), if you declare a variable
within that code block and use it as a predicate later, you will get
either a fixed guess for cardinality, or a less-confidence-inspiring
estimate than when the histogram is used.
The local variable effect discussed in the rest of this post produces
the same behavior as the OPTIMIZE FOR UNKNOWN hint, or executing
queries with sp_prepare.
That estimate will be based on the number of rows in the table, and
the “All Density” of the column multiplied together, for single
equality predicates. The process for multiple predicates depends on
which cardinality estimation model you’re using.
The guess you get depends on what kind of predicate you use.
Equality predicates multiply table cardinality by column selectivity:
And inequality predicates use different fixed percentages of table cardinality depending on which cardinality estimation model you're using.
From my blog post Yet Another Post About Local Variables
In a stored procedure (and even in ad hoc queries or within dynamic
SQL, like in the examples linked above), if you declare a variable
within that code block and use it as a predicate later, you will get
either a fixed guess for cardinality, or a less-confidence-inspiring
estimate than when the histogram is used.
The local variable effect discussed in the rest of this post produces
the same behavior as the OPTIMIZE FOR UNKNOWN hint, or executing
queries with sp_prepare.
That estimate will be based on the number of rows in the table, and
the “All Density” of the column multiplied together, for single
equality predicates. The process for multiple predicates depends on
which cardinality estimation model you’re using.
The guess you get depends on what kind of predicate you use.
Equality predicates multiply table cardinality by column selectivity:
And inequality predicates use different fixed percentages of table cardinality depending on which cardinality estimation model you're using.
Context
StackExchange Database Administrators Q#303748, answer score: 7
Revisions (0)
No revisions yet.