Nice to see someone else confirm my experience - e.g. I'm not crazy!  From my testing, temp tables are as good as or outperform table variables.  The clear advantage to me is the ability to pass a table variable as a parameter, but the use case for those seems limited, and the overhead (creating a table variable type) is too much.  Easier to just use temp tables, even then. 

I have heard that the optimizer / sql engine will convert large table variables to temporary tables anyway, under the covers.  Can anyone confirm?

Nice article.  Good to see someone quantify these differences with code. 

In many (most?) cases, the temporary structure is made to contain only the data that is actually needed.  The reason why that is important is because that usually means that full table scans will be made anyway which usually negates the need for any indexing.  There are always exceptions, of course.

Whether there are indexes or not, Temp Tables can frequently be batch loaded with minimal logging using SELECT INTO which can give Temp Tables a performance advantage over Table Variables.

There is another "performance" advantage that Temp Tables can have over Table Variables and you eluded to it.  The contents of a Temp Table will persist in SSMS allowing a programmer to incrimentally troubleshoot code without having to include all of the previous code that builds and populates a Table Variable.  That sometimes makes both development and troubleshooting go quite a bit faster.

To answer the question about the conversion of large Table Variables to Temp Tables...

Both Temp Tables and Table Variables build their structure in TempDB.  As with any good database engine, those structures start out in memory and spill to disk if they get too big.  You can typically see Table Variables in TempDB as tables that begin with a # sign and have an all numeric digit name.

In addition to what Jeff Moden said, you can see table variables in tempdb only from version 2008 onwards. Here is a post about it http://beyondrelational.com/modules/2/blogs/70/posts/13664/storage-of-table-variable.aspx

@Madhivanan

Not sure why you say just 2008. Even on 2000 you can see them in tempdb with

DECLARE @T TABLE (X INT)

SELECT *

FROM sysobjects

WHERE name LIKE '#%'

I've written a quite detailed piece about the differences between temporary tables and table variables here. 

(Make sure you are in tempdb if running the code above!)

This was a reasonable first test, and I'm glad to see actual numbers, but I'd have to suggest some other factors to look at, most of which are highly likely to favor #temp tables at many non-trivial data sizes.

1) For any unique column (even more with the unique, contiguous data you used) Table variable nonclustered unique indexes can be created using UNIQUE constraints (http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx#bm535734)

DECLARE @MyTabVar TABLE( PKCol intNOTNULLPRIMARYKEYCLUSTERED,UniqueCol intNOTNULLUNIQUE)

2) Non-integer, non-sequential datasets 
 2a) with and without rebuilding the clustered and/or nonclustered index after data additions index
 2b) including larger varchar types with duplicate data

3) Without any indexes at all on either - no primary key, no nothing. Particularly useful in the table scan cases Jeff mentioned

4) Multiple load phases - using temporary structures to run what's normally nasty row-by-row exception case logic only once for a given job.
 4a) Often this is gathering just the required and relevant and accurate data from several tables to be worked on by many queries later.
 4b) Combine with 2a's index rebuilds if required.

4) Multi-column indexes - some cases where temporary tables/table variables

One considering when persisting and modifying large temporary resultsets is transaction logging. I understand why temp tables are logged, because they are included in batch level transactions and so technically should be included in a rollback, although at least in my experience there rarely a practical use for that. However, with temp tables you have the option of creating the table using SELECT INTO, which will not not transaction log the inserts the same way it will if you first create the table and then insert it.

Table variables are not even included in transactions, just like other variables they arn't recovered from a rollback, but for some reason they are still transaction logged like temp tables. That's unfortunate, and if it were not the case, table variables would be clearly favored in a wider array of scenarios.

@Eric 

Table variables are subject to logging because if in an error occurs during the execution of the statement it needs to be rolled back. For example

 DECLARE @T TABLE

 (

 P INT PRIMARY KEY

 )

 INSERT INTO @T VALUES (1),(2)

  INSERT INTO @T VALUES (3),(4),(1)

  SELECT *  FROM @T

The statement encounters a PK uniqueness violation so the insert of values 3 & 4 is rolled back.

In 2008+ "INSERT ... SELECT" can be minimally logged in the same way as "SELECT ... INTO". Temporary tables can still have advantages when it comes to logging though. e.g table variables do not support "TRUNCATE". One logging advantage of table variables is that the system transactions on them are independent of the surrounding user transaction so a long running transaction that writes to table variables will not prevent the tempdb transaction log from being truncated.

@Martin Smith

Do information_schema.tables show up the table variables in version 2000? I think I said that keeping information_schma.tables in mind

@Madhivanan - Yes in MSDE 2000, running the following

declare @t table(i int)

select * 

from tempdb.INFORMATION_SCHEMA.TABLES  

WHERE TABLE_NAME LIKE '#%'

Returns

TABLE_CATALOG   TABLE_SCHEMA   TABLE_NAME    TABLE_TYPE

--------------- -------------- ------------- ----------

tempdb          dbo            #108B795B     BASE TABLE

Thanks for the excellent tip.  This confirms what I have generally seen as well.

Personally, I think table variables often result in slightly cleaner code and they can be passed as parameters when that matters.  But temp tables will almost always perform at least as well and often better, and they give you more options for indexing.  Generally, I use a table variable when I am dealing with very small datasets (less than 100 rows) and temp tables the rest of the time.

my 2 cents the basic difference when we ask anybody  is that table variables are memory resident whereas temporary tables are temdb resident which is very untrue which is been proven in many blog posts and can be proved with the below code snippet

use tempdb

---this would list all the existing #tables used by other applications can list down or can try it in a test environment

select * from sys.tables where name like '%#%'

GO
-- a very small table

declare @table as TABLE

(

id int

)

select * from sys.tables where name like '%#%'

---You will see we have a # table which is for table variable---

But to me the question arises what is the reason of keeping table variable if we have a similar temprary table. I could only see the case of functions otherwise i could not get any reason where we can justify the existance of table variable.

Another huge deal is the statistics..

Table varible don't keep statistics, this causes very poor performance when doing joins ex..

Temp table do keeps statistic!

Thanks for the article Ben. Not the results I would have expected.