Sergiy (3/18/2013)
Sean Lange (3/18/2013)
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.If you come back to where it's started you'll see it's exactly what I said.
The only difference is as minor as an extra line of code required for syntax sake only.
I'm not so sure about that "in-memory" thing, therefore I did not bring it up, until you guys forced me. 🙂
But I know there is controversy around it.
That's why I prefer (and suggest) not to use CTE where it's not necessary.
Again, it's my preference.
I don't think we disagreed on this point. I joined the conversation simply because you said that more keystrokes is more complex. That to me was completely ridiculous so I joined in.
It makes no sense at all to think that a CTE cannot live at least partially in tempdb. There is no way that the sql team was that short sighted. This is nothing more than extension of the fallacy that temp tables are memory only. It just doesn't make sense.
As for the article discussing CTE's doing a self join, that is a well known performance issue with CTEs.
_______________________________________________________________
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/