I recently saw an answer to this question on dba.stackexchange.com written by Martin Smith. It was probably one of the most complete answers to this question I have ever seen. In fact it’s probably one of the most complete answers possible. I highly recommend that you read it. In the mean time here is a summary:

• Table variables are actually stored in tempdb just like temporary tables.
• With respect to default collation, user defined data types and xml collections table variables act like they are part of the local database, temporary tables act like they are part of tempdb.
• Temp tables have a much wider scope. If created at the outer scope (@@NESTLEVEL = 0) they can span batches.
• Rollback will affect temp tables but not table variables.
• Table variables do not support TRUNCATE.
• Column statistics are maintained for temp tables not table variables.
• Indexes: Prior to SQL 2014 table variables only support indexes if created implicitly by creating a unique constraint or primary key. Post SQL 2014 table variables do support non-clustered indexes when declared in line. Also table variables do not support INCLUDE columns, filtered indexes or partitioning. Temp tables seem to fully support indexing (although the partitioning scheme would have to be created in tempdb of course).
• Queries with table variables don’t get parallel plans, the same is not true with temp tables.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, microsoft sql server, T-SQL