Josh Ashwood (8/9/2011)
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 ?
It depends. Really.
It'll vary with the size and complexity of what you're doing. Simpler queries will probably be better kept in one or with CTEs (if it makes things easier to read). More complex ones may benefit from being split up and run in bits.
The major difference that you must realise is that temp tables and table variables are both physical structures - they both store the data in an intermediate state. CTEs are not tables, they're just named subqueries, so they're not splitting out the processing, doing some now some later. It's still a single query.
Table variables, because of no statistics, can often be problematic on larger row counts (where anything over a couple hundred is probably large)
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass