SQL Server Temp table vs Table variable

Hi All,

Some time back I was in an interview where one of the question was "Whats the difference between Temporary tables and Table variables".

What I knew at that time was:

• Table variables are a newly introduced concept  and are faster.
• Table variable exists only exist in current session.

So after interview I googled a bit and as-usual found answer on SQL Authority spread in couple of articles.  These articles have a lot of comments and some interesting points, so I thought to summarize them here for those who want it quick:

• In case of large amount of data temp table is advised because SQL Server creates, maintains and uses the statistics of temp table while generating the execution plan. Besides this we can create index in temp tables if that is needed.
• Table variables performs good only for small record-set. Because there data statistics is not used while creating the execution plan and because of that sometimes fully optimized plan are not created. Table variable should be used where they are joined with small tables only.
• Temp table has the global option which is very useful too when having two connections open and running multiple tests at the same time.
• Table variables are not transactional and do not roll back. Temp tables are transactional and do roll back.

For more details, please visit below links:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/