SQL server Temporary Tables, Table Variables and Recompiles - interesting scenario

Usage of Temporary tables or variables is a common scenario in SQL Server world, since SQL Server 2005 usage of TEMPDB references to the blog posts here: http://sqlserver-qa.net/blogs/tools/archive/2007/04/05/sql-server-index-optimization-best-practices.aspx, http://sqlserver-qa.net/blogs/tools/archive/2007/04/19/what-to-do-when-tempdb-is-full-in-sql-2000-2005-versions.aspx & http://sqlserver-qa.net/blogs/tools/archive/tags/tempdb/default.aspx will get you how busy it will be as compared to previous versions.

As per the engine features from SQL Server 2005 both are materialized as temporary tables in the tempdb database. It is entirely differnt in case of using table variables as they tend to work on actual metadata tables and in few cases they can provide superior performance. This is because table variables do not maintain either statistics or indexes, resulting in lower overhead. As one of the Technet documentation refers that if your table contents in using table variable will not exceed a few 8KB-sized database pages that the database engine will keep resident in the data cache, then table variables are preferred.

It is a open truth that heavily used tempdb will have contention, but in rare situations you would see heavy blocking against metadata tables in addition to TEMPDB. Similar to this KBA328851 talks about such blocking when creating and deleting large numbers of temp tables all at the same time. To some extent using such temporary tables is acceptable, but when it is hurting the performance then better to overview this practice as increased performance you gain when pulling data from the temporary table outweighs the overhead in building the table in the first place. The lifespan of these objects are limited and upto the frequency of connection, think about the number of active instances of the temp table(s) created and deleted that will have contention in terms of hardware too. I would recommend further read-on about FAQ  on "Frequently Asked Questions - SQL Server 2000 - Table Variables" that will talk about table variables & temp tables usage.

Further within SQL 2005 version Programmability & API development team in Microsoft has written an excellent explanation on recomplies of table variables and temporary tables when they are used heavily, refer to that blog http://blogs.msdn.com/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx here