Josh Ashwood (8/9/2011)
Nice article, and definitely on topic for real database developers...However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ?
An 'It depends' answer would be ok, if expanded on!
Answer : CTE and leave the damn thing in one huge query should be the same because they do the same
Table variables have the advantage that are defined witih the session
Best performance is with temp tables, which are regular tables stored into the db tempd
I want to underline that on temp tables is possible to create indexes and statistics , and this can really improve the performance