• In my experience the only sure fire way of deciding is to test alternative methods of your code with real life data volumes. Temp variables are better performers in simple scenarios.

    However, whilst optimising a long running process recently I found that Temp tables beat temp variables by a factor of 10 when used in complex queries. reduced a 48:00 minute procedure run to 4:26 !!!

    The consensus at the time was due to the query optmizer choosing a sub-optimal plan because it assumes temp variables have only 1 row! (I didn't prove this but initial query analyser execution plans showed it to be true)

    My personal rule of thumb is assume temp tables are quicker if the scenario is complex, and use temp variables if a) you have to for other technical reasons, or b) where the subsequent use of the temp variable data is in simple queries (eg. zero joins).

    I hope this helps!