Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?
While both the temp table and the table variable will utilize the TempDB essentially the same way (IE. Memory till table size reaches a certain point), the table variable will not write to the transaction log (which is why it is unaffected by Rollbacks). This can decrease the overall I/O used by an operation. Other consideration include statistics. for a table variable, there is always considered to be 1 row by the optimizer, even if there are tens of thousands, which can result in a less than optimum choice for the join (nested loop vs. hash vs. merge). also, Temp Tables can trigger recompiles of stored procedures that create the object, as the acutal execution plan cannot be persisted (table reference doesn' exist until you are executing), which can also be costly.