September 24, 2016 at 7:51 am
Do you know if a passed parameter is handled the same way as a variable once in a stored proc?
Basically, yes.
The only difference appears when you have a value of a variable hard coded in T-SQL. Then optimiser can base its decision on the most effective execution plan on that value.
There is a lack of constant declaration inT-SQL, if you intend to reuse a constant you have to declare it as variable.
This creates a bit of confusion which Luis fell to: value of a variable can be considered by optimiser only if it's assigned in the code.
If a value came from a query - well, optimiser can try to use statistics on the table the value is loaded from, but still, the "compiled value" will be pretty much as uncertain as for a parameter.
_____________
Code for TallyGenerator
September 24, 2016 at 8:36 am
Sergiy (9/24/2016)
Do you know if a passed parameter is handled the same way as a variable once in a stored proc?
Basically, yes.
The only difference appears when you have a value of a variable hard coded in T-SQL. Then optimiser can base its decision on the most effective execution plan on that value.
There is a lack of constant declaration inT-SQL, if you intend to reuse a constant you have to declare it as variable.
This creates a bit of confusion which Luis fell to: value of a variable can be considered by optimiser only if it's assigned in the code.
If a value came from a query - well, optimiser can try to use statistics on the table the value is loaded from, but still, the "compiled value" will be pretty much as uncertain as for a parameter.
Sergiy's right-on for the sniffed parameter value in the plan.
From a usage perspective, the difference is what Luis already pointed out. Table-valued parameters are read-only, while table variables are not.
September 24, 2016 at 8:39 am
thank you both for the clarification....
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply