I came to this forum to mention the inaccuracies around the table variable being stored in memory, but since that has already been covered several times I will just mention that a cool feature of 2008 is that table variables can be used in parameters for stored procedures.

The table variable always seemed so limited to me without this feature because if you can only access the table inside of a stored procedure why not just create a local temp table - much easier for debugging.

In several cases I have used global temp tables when I needed to persist data across dynamic SQL and/or inner procedures, but that is prone to collisions. The table variable will act much like a semi-persistent result set or array in 2008 which will make it useful.

In my opinion whether derived tables and/or CTEs are "tables in any sense" is not really relevant. What is relevant is that they can, and probably should, be used in many situations where many would use some sort of temporary table. For example, they have a significant scope advantage over the other pretenders. That is why it makes sense to discuss them in an article on temporary tables.

As far as the relational model goes tables, view, CTEs and other relations are supposed to be equivalent anyway. It is only when you look beyond the relational model and consider things like performance that the distinction should become relevant. I am not saying performance issues are not important, but I think they should be discussed in the context of a relational approach.

Recently I have experimented with derived table and also table variable.

For the simple examples below, using derived table, the query took about 1 min 17 sec to complete, and using the table variable, the query took about 4 sec to complete. I can't explain why it happens this way.

I have tried a few more examples, and it is still the table variable that wins out.