Sergiy (3/18/2013)
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.Hmm...
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.
But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.
Do you agree with me on this?
Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference. My preference is the age old "it depends". I find that sometimes I prefer one over the other. I do often find that using a CTE can make an otherwise complicated query a bit easier to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/