October 3, 2011 at 1:08 pm
SanDroid (10/3/2011)
Does anyone know what the difference would be between using a WITH statement and a TEMP table?
A CTE does not (generally) materialize the interim result set. Using a temporary table will materialize the result set, and you'll get distribution statistics too. The reason for the (generally) qualifier is that recursive CTEs do materialize the set (in a stacked index spool). It is helpful to think of (non-recursive) CTEs as inline VIEW definitions.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 3, 2011 at 1:10 pm
SQL Kiwi (10/3/2011)
The skilled database professional is aware of the benefits of each type of temporary structure, and uses each as appropriate. Anyone who says one is always better than the other is wrong.
That's 100% true. If we had a mechanism for plus votes I would be using it!
The other thing the skilled professional generally does is test and measure to check that his beliefs apply in particular cases
Tom
October 3, 2011 at 1:47 pm
L' Eomot Inversé (10/3/2011)
If we had a mechanism for plus votes I would be using it!
I think I've seen this exact conversation recently with quoter and quoted inversed.
I just can't remember where... :hehe:
-- Gianluca Sartori
October 3, 2011 at 3:38 pm
This question was good.
October 4, 2011 at 7:49 am
SQL Kiwi (10/3/2011)
SanDroid (10/3/2011)
Does anyone know what the difference would be between using a WITH statement and a TEMP table?A CTE does not (generally) materialize the interim result set. Using a temporary table will materialize the result set, and you'll get distribution statistics too. The reason for the (generally) qualifier is that recursive CTEs do materialize the set (in a stacked index spool). It is helpful to think of (non-recursive) CTEs as inline VIEW definitions.
Nice, thanks for that.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 5, 2011 at 6:35 pm
I reckon the solution description to this question needs to be updated ASAP.
Many SQL expert agrees and knows that table variable do not cause recompile, whilst the solution clearly describes the performance was caused by table variable is recompiling each time.
A very disturbing description to be left as it is for long time because many people reads sqlservercentral and word gets passed around and eventually contributes to a "myth".
I'm one of the few who would be very interested to know if someone can prove that table variable causes recompile. Logically speaking, table variables do not have statistics at all and I would wonder the reason for SQL to recompile. The row estimates of table variable in query plan would be 1 anyway.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
November 27, 2011 at 8:34 am
As always the real answer is test, test and more tests...
But looking at the stats and the output of the 2 statements, I will not be feeling guilty about using temp tables...
Thanks for bringing this up and having everyone comment.
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply