Table variables would so useful if they worked. Imagine if we instead of implicitly sending table data around and checking if it exists and holds something sensible, could just explicitly send it in parameter...but no who needs that...
@@ErikDarlingData Yeah, they are awesome for passing tables along in a controlled and explicit way, but then there is what you describe here and temp tables are just better anyway....
Oh I see, yes. Passing temp tables between procedures does have odd effects on the plan cache, but that's often the least of my concerns. The behavior is interesting though.
2 дня назад
Temp tables do a good job. I would prefer having the "I need X, Y and Z in this structure to do what you want" to be in actual code without preformance impacts rather then a comment and a if object_id is null when I create a procedure though :)
Interesting timing on this video. Just today, I rejected a pull request where the developer used a @tableVariable instead of a #tempTable. I did my own comparisons just now between @tableVariables and #tempTables myself similar to your test scenario. What is revealing in the Properties of the final SELECT of the query plan is that the query using the #tempTable has OptimizerStatsUsage available to it whereas the query using the @tableVariable does not. It’s as if the SQL Optimizer is saying “You go figure it out yourself! Good luck!” As for your estimation of 3 rows on your Posts table query, I wonder if there is a cached plan somewhere that is pinning usage of that table to think it has 3 rows for certain JOIN scenarios. As a Dev Manager having DBA and DB Developer experience since SQL Server 2000, I wish Microsoft would just sunset table variables and be done with this nonsense; or maybe under the cover start treating @tableVariables like #tempTables. The dynamically generated #tablename is already there for the @tableVariable in TEMPDB during its usage. 🙄
Yes, that’s a very good way of illustrating the difference. Much easier than the usual assortment of trace flags. I’m not sure what you’re on about with the cached plan stuff. I was using recompile hints.
I find table variables useful mainly for preserving data after a transaction rollback. Otherwise, I prefer temporary tables for their flexibility and performance.
@@ErikDarlingData Oh, well, I have in some data warehouse processing. It was useful for identifying conditions that caused errors and logging them after a rollback.
Yeah, that’s about exactly where I’d expect to see it. Good for you on actually following through!
3 дня назад+1
But you need to declare it before opening the transaction or smt right? So you dump the data to declared table variables then to a log if the transaction fails? Still no way of breaking out of a transaction and logging if your code is initially called inside a transaction?
Table variables would so useful if they worked. Imagine if we instead of implicitly sending table data around and checking if it exists and holds something sensible, could just explicitly send it in parameter...but no who needs that...
You mean like a table valued parameter?
@@ErikDarlingData Yeah, they are awesome for passing tables along in a controlled and explicit way, but then there is what you describe here and temp tables are just better anyway....
Oh I see, yes. Passing temp tables between procedures does have odd effects on the plan cache, but that's often the least of my concerns. The behavior is interesting though.
Temp tables do a good job. I would prefer having the "I need X, Y and Z in this structure to do what you want" to be in actual code without preformance impacts rather then a comment and a if object_id is null when I create a procedure though :)
The always important caveat "outside of New Zealand"
Don't want to face the Wrath of the Jandal
Interesting timing on this video. Just today, I rejected a pull request where the developer used a @tableVariable instead of a #tempTable.
I did my own comparisons just now between @tableVariables and #tempTables myself similar to your test scenario. What is revealing in the Properties of the final SELECT of the query plan is that the query using the #tempTable has OptimizerStatsUsage available to it whereas the query using the @tableVariable does not. It’s as if the SQL Optimizer is saying “You go figure it out yourself! Good luck!”
As for your estimation of 3 rows on your Posts table query, I wonder if there is a cached plan somewhere that is pinning usage of that table to think it has 3 rows for certain JOIN scenarios.
As a Dev Manager having DBA and DB Developer experience since SQL Server 2000, I wish Microsoft would just sunset table variables and be done with this nonsense; or maybe under the cover start treating @tableVariables like #tempTables. The dynamically generated #tablename is already there for the @tableVariable in TEMPDB during its usage. 🙄
Yes, that’s a very good way of illustrating the difference. Much easier than the usual assortment of trace flags. I’m not sure what you’re on about with the cached plan stuff. I was using recompile hints.
I find table variables useful mainly for preserving data after a transaction rollback. Otherwise, I prefer temporary tables for their flexibility and performance.
Everyone says this, but I've never seen anyone actually doing it.
@@ErikDarlingData Oh, well, I have in some data warehouse processing. It was useful for identifying conditions that caused errors and logging them after a rollback.
Yeah, that’s about exactly where I’d expect to see it. Good for you on actually following through!
But you need to declare it before opening the transaction or smt right? So you dump the data to declared table variables then to a log if the transaction fails?
Still no way of breaking out of a transaction and logging if your code is initially called inside a transaction?
never using table vars, very tricky and very unexpecable in real world applications. it is there on SQL just like that, for no reason! 😅
There are good reasons for them, performance just isn’t one of them.