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

User · Accepted Answer

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

User · Answer

Quote taken from  Professional SQL Server 2012 Internals and Troubleshooting     Statistics   The major difference between temp tables and table variables is that   statistics are not created on table variables  This has two major   consequences  the fi rst of which is that the Query Optimizer uses a   fi xed estimation for the number of rows in a table variable   irrespective of the data it contains  Moreover  adding or removing   data doesn   t change the estimation       Indexes You can   t create indexes on table variables although you can   create constraints  This means that by creating primary keys or unique   constraints  you can have indexes  as these are created to support   constraints  on table variables  Even if you have constraints  and   therefore indexes that will have statistics  the indexes will not be   used when the query is compiled because they won   t exist at compile   time  nor will they cause recompilations       Schema Modifications Schema modifications are possible on temporary   tables but not on table variables  Although schema modifi cations are   possible on temporary tables  avoid using them because they cause   recompilations of statements that use the tables      TABLE VARIABLES ARE NOT CREATED IN MEMORY  There is a common misconception that table variables are in-memory structures and as such will perform quicker than temporary tables  Thanks to a DMV called  sys   dm   db   session   space   usage   which shows tempdb usage by session  you can prove that   s not the case  After restarting SQL Server to clear the DMV  run the following script to confi rm that your  session   id returns  0 for user   objects   alloc   page   count    SELECT session id  database id  user objects alloc page count FROM sys dm db session space usage WHERE session id  gt  50     Now you can check how much space a temporary table uses by running the following     script to create a temporary table with one column and populate it with one row   CREATE TABLE  TempTable   ID INT     INSERT INTO  TempTable   ID   VALUES   1     GO SELECT session id  database id  user objects alloc page count FROM sys dm db session space usage WHERE session id  gt  50     The results on my server  indicate that the table was allocated one page in tempdb  Now run the same script but use a table variable this time   DECLARE  TempTable TABLE   ID INT     INSERT INTO  TempTable   ID   VALUES   1     GO SELECT session id  database id  user objects alloc page count FROM sys dm db session space usage WHERE session id  gt  50     Which one to Use      Whether or not you use temporary tables or table variables should be   decided by thorough testing  but it   s best to lean towards temporary   tables as the default because there are far fewer things that can go   wrong         I   ve seen customers develop code using table variables because  they   were dealing with a small amount of rows  and it was quicker than  a   temporary table  but a few years later there were hundreds of    thousands of rows in the table variable and performance was terrible     so try and allow for some capacity planning when you make your    decision

User · Answer

Differences between Temporary Tables    temp  temp  and Table Variables   table  are as   Table variable   table  is created in the memory  Whereas  a Temporary table    temp  temp  is created in the tempdb database  However  if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb   Table variables cannot be involved in transactions  logging or locking  This makes  table faster then  temp  So table variable is faster then temporary table   Temporary table allows Schema modifications unlike Table variables   Temporary tables are visible in the created routine and also in the child routines  Whereas  Table variables are only visible in the created routine   Temporary tables are allowed CREATE INDEXes whereas  Table variables aren   t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint

User · Answer

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    Successful Insert  Multi Row Insert where statement rolled back due to constraint violation  Update Delete 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   gt   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   gt   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                ----------------------- -------------------- --------------------------- --------------- ------ --------------- ------ ------------------

User · Answer

In which scenarios does one out-perform the other    For smaller tables  less than 1000 rows  use a temp variable  otherwise use a temp table

User · Answer

The other main difference is that table variables don t have column statistics  where as temp tables do  This means that the query optimiser doesn t know how many rows are in the table variable  it guesses 1   which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows

User · Answer

wcm - actually to nit pick the Table Variable isn t Ram only - it can be partially stored on disk   A temp table can have indexes  whereas a table variable can only have a primary index  If speed is an issue Table variables can be faster  but obviously if there are a lot of records  or the need to search the temp table of a clustered index  then a Temp Table would be better   Good background article

User · Answer

It surprises me that no one mentioned the key difference between these two is that the temp table supports parallel insert while the table variable doesn t  You should be able to see the difference from the execution plan  And here is the video from SQL Workshops on Channel 9  This also explains why you should use a table variable for smaller tables  otherwise use a temp table  as SQLMenace answered before

User · Answer

Temp table  A Temp table is easy to create and back up data  Table variable  But the table variable involves the effort when we usually create the normal tables   Temp table  Temp table result can be used by multiple users  Table variable  But the table variable can be used by the current user only     Temp table  Temp table will be stored in the tempdb  It will make network traffic  When we have large data in the temp table then it has to work across the database  A Performance issue will exist  Table variable  But a table variable will store in the physical memory for some of the data  then later when the size increases it will be moved to the tempdb   Temp table  Temp table can do all the DDL operations  It allows creating the indexes  dropping  altering  etc    Table variable  Whereas table variable won t allow doing the DDL operations  But the table variable allows us to create the clustered index only   Temp table  Temp table can be used for the current session or global  So that a multiple user session can utilize the results in the table  Table variable  But the table variable can be used up to that program   Stored procedure   Temp table  Temp variable cannot use the transactions  When we do the DML operations with the temp table then it can be rollback or commit the transactions  Table variable  But we cannot do it for table variable   Temp table  Functions cannot use the temp variable  More over we cannot do the DML operation in the functions   Table variable  But the function allows us to use the table variable  But using the table variable we can do that   Temp table  The stored procedure will do the recompilation  can t use same execution plan  when we use the temp variable for every sub sequent calls  Table variable  Whereas the table variable won t do like that

User · Answer

Another difference   A table var can only be accessed from statements within the procedure that creates it  not from other procedures called by that procedure or nested dynamic SQL  via exec or sp executesql    A temp table s scope  on the other hand  includes code in called procedures and nested dynamic SQL   If the table created by your procedure must be accessible from other called procedures or dynamic SQL  you must use a temp table   This can be very handy in complex situations

User · Answer

For all of you who believe the myth that temp variables are in memory only   First  the table variable is NOT necessarily memory resident  Under memory pressure  the pages belonging to a table variable can be pushed out to tempdb   Read the article here  TempDB   Table variable vs local temporary table

User · Answer

Consider also that you can often replace both with derived tables which may be faster as well  As with all performance tuning  though  only actual tests against your actual data can tell you the best approach for your particular query

[sql-server] What's the difference between a temp table and table variable in SQL Server?

Examples related to sql-server

Examples related to temp-tables

Examples related to table-variable