What's the difference between a temp table and table variable in SQL Server?

Question

In SQL Server 2005, we can create temp tables one of two ways:

declare @tmp table (Col1 int, Col2 int); 

or

create table #tmp (Col1 int, Col2 int); 

What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.

In which scenarios does one out-perform the other?

Answer 1

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

• Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

• Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.

• Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

• Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

• You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

• You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).

• Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

• Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

• Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:

• Martin Smith's great answer on dba.stackexchange.com

• MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977

• MDSN blog article: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

• Article: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com

Answer 2

Just looking at the claim in the accepted answer that table variables don't participate in logging.

It seems generally untrue that there is any difference in quantity of logging (at least for insert/update/delete operations to the table itself though I have since found that there is some small difference in this respect for cached temporary objects in stored procedures due to additional system table updates).

I looked at the logging behaviour against both a @table_variable and a #temp table for the following operations.

1. Successful Insert
2. Multi Row Insert where statement rolled back due to constraint violation.
3. Update
4. Delete
5. Deallocate

The transaction log records were almost identical for all operations.

The table variable version actually has a few extra log entries because it gets an entry added to (and later removed from) the sys.syssingleobjrefs base table but overall had a few less bytes logged purely as the internal name for table variables consumes 236 less bytes than for #temp tables (118 fewer nvarchar characters).

Full script to reproduce (best run on an instance started in single user mode and using sqlcmd mode)

:setvar tablename "@T"  :setvar tablescript "DECLARE @T TABLE"  /*  --Uncomment this section to test a #temp table :setvar tablename "#T"  :setvar tablescript "CREATE TABLE #T" */  USE tempdb  GO     CHECKPOINT  DECLARE @LSN NVARCHAR(25)  SELECT @LSN = MAX([Current LSN]) FROM fn_dblog(null, null)    EXEC(N'BEGIN TRAN StartBatch SAVE TRAN StartBatch COMMIT  $(tablescript) ( [4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0, InRowFiller char(7000) DEFAULT ''A'', OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000), LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000) )   BEGIN TRAN InsertFirstRow SAVE TRAN InsertFirstRow COMMIT  INSERT INTO $(tablename) DEFAULT VALUES  BEGIN TRAN Insert9Rows SAVE TRAN Insert9Rows COMMIT   INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0]) SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM sys.all_columns  BEGIN TRAN InsertFailure SAVE TRAN InsertFailure COMMIT   /*Try and Insert 10 rows, the 10th one will cause a constraint violation*/ BEGIN TRY INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0]) SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20 FROM sys.all_columns END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH  BEGIN TRAN Update10Rows SAVE TRAN Update10Rows COMMIT  UPDATE $(tablename) SET InRowFiller = LOWER(InRowFiller),     OffRowFiller  =LOWER(OffRowFiller),     LOBFiller  =LOWER(LOBFiller)   BEGIN TRAN Delete10Rows SAVE TRAN Delete10Rows COMMIT  DELETE FROM  $(tablename) BEGIN TRAN AfterDelete SAVE TRAN AfterDelete COMMIT  BEGIN TRAN EndBatch SAVE TRAN EndBatch COMMIT')   DECLARE @LSN_HEX NVARCHAR(25) =          CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +         CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +         CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)          SELECT      [Operation],     [Context],     [AllocUnitName],     [Transaction Name],     [Description] FROM   fn_dblog(@LSN_HEX, null) AS D WHERE  [Current LSN] > @LSN    SELECT CASE          WHEN GROUPING(Operation) = 1 THEN 'Total'          ELSE Operation        END AS Operation,        Context,        AllocUnitName,        COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],        COUNT(*)                              AS Cnt FROM   fn_dblog(@LSN_HEX, null) AS D WHERE  [Current LSN] > @LSN   GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),()) 

Results

+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ |                       |                    |                           |             @TV      |             #TV      |                  | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  | | LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  | | LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  | | LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  | | LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  | | LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  | | LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  | | LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              | | LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  | | LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  | | LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  | | LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  | | LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  | | LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  | | LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  | | LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             | | LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  | | LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  | | LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              | | LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  | | LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  | | LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  | | LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  | | LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  | | LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  | | LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  | | LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  | | LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  | | LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  | | LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  | | LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  | | LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  | | LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  | | LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  | | LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+