I just had my attention drawn to this via its mention in the UT Magazine...

There is a big shortcoming with @table variables that you haven't mentioned:

If you include a SELECT statement in your code where you JOIN with a table type variable, the only access method that can be used is a table scan. Consequently, it is usually far faster to create a work table, truncate that at the beginning of your code, and then populate that with your result set. I have turned multi-hour queries into the virtually instant answer sort with this technique.

I did contact SQLWish to mention this and suggest extending the table type variables to support dot notation and the ability to index them.

Some time back, Microsoft in the UK were unable to say how these variables actually work, i.e. is there a threshold point at which the number of rows in the variable actually causes it to physically be created in tempdb etc. -has that ever been established?

Regards
Simon