Differences between SQL Server temporary tables and table variables

By:   |   Updated: 2008-08-06   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | 5 | > Temp Tables


Problem

I've heard of table variables, but not sure how to use them in a stored procedure. What purpose do they serve and why not just use temporary tables instead?

Solution

If you already know how to create and use a temporary (temp) table then you're going to have no problem understanding how to use a table variable. The usage is just about identical. I'd like to spend the first part of this tip discussing temp tables and their use before we move onto your question for those who may not be so familiar.

Temporary Tables

Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.

The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):

CREATE TABLE dbo.#Cars 
   (   
   Car_id int NOT NULL, 
   ColorCode varchar(10), 
   ModelName varchar(20), 
   Code int, 
   DateEntered datetime 
   )

Temporary tables act like physical tables in many ways. You can create indexes and statistics on temporary tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys. You can also add and drop columns from temporary tables. For example, if I wanted to add a default value to the DateEntered column and create a primary key using the Car_id field I would use the following syntax:

ALTER TABLE dbo.#Cars 
   ADD CONSTRAINT [DF_DateEntered] DEFAULT (GETDATE()) FOR [DateEntered], 
   PRIMARY KEY CLUSTERED 
   ( [Car_id] ) ON [PRIMARY] 
GO

Table Variables

The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:

DECLARE @Cars table 
   ( 
   Car_id int NOT NULL, 
   ColorCode varchar(10), 
   ModelName varchar(20), 
   Code int, 
   DateEntered datetime 
   )

As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability). As with any other local variable in T-SQL, the table variable must be prefixed with an "@" sign. Unlike temporary or regular table objects, table variables have certain clear limitations.

  • Table variables can not have Non-Clustered Indexes
  • You can not create constraints in table variables
  • You can not create default values on table variable columns
  • Statistics can not be created against table variables

Similarities with temporary tables include:

  • Instantiated in tempdb
  • Clustered indexes can be created on table variables and temporary tables
  • Both are logged in the transaction log
  • Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

Usage Temp Table vs Table Variable

Temporary tables are usually preferred over table variables for a few important reasons: they behave more like physical tables in respect to indexing and statistics creation and lifespan. An interesting limitation of table variables comes into play when executing code that involves a table variable. The following two blocks of code both create a table called #Cars and @Cars. A row is then inserted into the table and the table is finally queried for its values.

--Temp Table: 
CREATE TABLE dbo.#Cars 
   ( 
   Car_id int NOT NULL, 
   ColorCode varchar(10), 
   ModelName varchar(20), 
   Code int, 
   DateEntered datetime 
   ) 

INSERT INTO dbo.#Cars (Car_id, ColorCode, ModelName, Code, DateEntered) 
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 

SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM dbo.#Cars 

DROP TABLE dbo.[#Cars]

This returns the following results:

01

--Table Variable: 
DECLARE @Cars TABLE 
   ( 
   Car_id int NOT NULL, 
   ColorCode varchar(10), 
   ModelName varchar(20), 
   Code int , 
   DateEntered datetime 
   ) 

INSERT INTO @Cars (Car_id, ColorCode, ModelName, Code, DateEntered) 
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 

SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM @Cars

The results differ, depending upon how you run the code. If you run the entire block of code the following results are returned:

02

However, you receive an error if you don't execute all the code simultaneously:

Msg 1087, Level 15, State 2, Line 1 
Must declare the table variable "@Cars"

What is the reason for this behavior? It is quite simple. A table variable's lifespan is only for the duration of the transaction that it runs in. If we execute the DECLARE statement first, then attempt to insert records into the @Cars table variable we receive the error because the table variable has passed out of existence. The results are the same if we declare and insert records into @Cars in one transaction and then attempt to query the table. If you notice, we need to execute a DROP TABLE statement against #Cars. This is because the table persists until the session ends or until the table is dropped.

So, it would appear that I don't advocate the use of table variables. That is not true. They serve a very useful purpose in returning results from table value functions. Take for example the following code for creating a user-defined function that returns values from the Customers table in the Northwind database for any customers in a given PostalCode:

CREATE FUNCTION dbo.usp_customersbyPostalCode ( @PostalCode VARCHAR(15) ) 
RETURNS 
   @CustomerHitsTab TABLE ( 
      [CustomerID] [nchar] (5), 
      [ContactName] [nvarchar] (30), 
      [Phone] [nvarchar] (24), 
      [Fax] [nvarchar] (24) 
   ) 
AS 
BEGIN 
   DECLARE @HitCount INT 

   INSERT INTO @CustomerHitsTab 
   SELECT 
      [CustomerID], 
      [ContactName], 
      [Phone], 
      [Fax] 
   FROM [Northwind].[dbo].[Customers] 
   WHERE PostalCode = @PostalCode 

   SELECT @HitCount = COUNT(*) FROM @CustomerHitsTab 

   IF @HitCount = 0 
   --No Records Match Criteria 
      INSERT INTO @CustomerHitsTab ( 
         [CustomerID], 
         [ContactName], 
         [Phone], 
         [Fax] ) 
      VALUES ('','No Companies In Area','','') 

   RETURN 
END 
GO

The @CustomerHitsTab table variable is created for the purpose of collecting and returning results of a function to the end user calling the dbo.usp_customersbyPostalCode function.

SELECT * FROM dbo.usp_customersbyPostalCode('1010')

03

SELECT * FROM dbo.usp_customersbyPostalCode('05033')

04

An unofficial rule-of-thumb for usage is to use table variables for returning results from user-defined functions that return table values and to use temporary tables for storage and manipulation of temporary data; particularly when dealing with large amounts of data. However, when lesser row counts are involved, and when indexing is not a factor, both table variables and temporary tables perform comparably. It then comes down to preference of the individual responsible for the coding process.

Next Steps
  • Review related tips about tempdb here at MSSQLTips.com:
  • Create a stored procedure that uses both a temporary table and a table variable in your test environment. Insert and return varying amounts of data from each and observe performance results for each.
  • Create a user-defined function that returns a table result. Compare performance against a similar SELECT statement with a WHERE clause that returns the same results on a large table in your test environment. What does performance look like for each scenario? Is a UDF slower or faster than a SELECT query that returns the same results?


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-08-06

Comments For This Article




Monday, February 3, 2020 - 9:24:06 PM - Niyazi Back To Top (84135)

Great explanation.. Thank you!

Monday, February 11, 2019 - 3:21:02 AM - Mike White Back To Top (79002)

Table Variables also come in very handy if fo whatever reason you have a Read Only account on a SQL server and can't create Temporary Tables

Thursday, February 11, 2016 - 9:34:56 AM - Greg Robidoux Back To Top (40649)

Hi Krunal,

A session is a connection to SQL Server.  So if you use SQL Server Management Studio each time you open a new query window that creates a new session/connection.

It is possible that an application can create multiple connections to SQL Server and if you use a #temp table that may be available to one of the connections, but not both connections. 

Another option is to use a ##temp table ( two #  ), which is a global temporary table.  This temp table is that available to any session/connection. Once the session/connection that created the ##temp table is closed the ##temp table goes away.

-Greg

Thursday, February 11, 2016 - 8:41:14 AM - Krunal Vyas Back To Top (40648)

Hi,

the Temporary table (#temp) persists until the "session ends" or until the table is dropped.

What does it means - "Session Ends"?

Can you please provide me a link on SQL sessions, Life of SQL Sessions?

And Practically when my C# Application opens connection and closes the connection , is it a SQL session?

It looks to me that when I open a new query window in SSMS, it creates new session! But I do not deep knowledge about it.

Please enlight on this topic. 

 

Monday, March 23, 2015 - 3:02:13 AM - mansi Back To Top (36633)

Hi,

Below is the text snippets from the article you spoke about.

  • Table variables can not have Non-Clustered Indexes
  • You can not create constraints in table variables
  • You can not create default values on table variable columns
  • Statistics can not be created against table variables

I have objection that temporary table

  • Table variables have Non-Clustered Indexes
  • You can  create constraints in table variables
  • You can  create default values on table variable columns
  • Statistics can be created against table variables

declare @tbl table (

 id int identity primary key,

 Col1 varchar(20) unique,

 Col2 varchar(30),

 Col3 varchar(20) default getdate(),

 unique nonclustered (Col2, Col3, id)

)

select * from @tbl

 

insert into @tbl (col1,col2)values('c1','c2')

select * from @tbl

Tuesday, March 10, 2015 - 4:39:40 AM - Chandan Kumar Back To Top (36476)

Hi,

Below is the text snippets from the article you spoke about.

"You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys"

I have objection that temporary table and use foreign key. Click below for more information:
https://msdn.microsoft.com/en-IN/library/ms189049.aspx

 

Monday, August 11, 2014 - 7:18:13 PM - Michael Green Back To Top (34087)

"A table variable's lifespan is only for the duration of the transaction that it runs in." - don't you mean "batch that it runs in"?  If I submit a number of statements which includes the declaration of a table variable, I can reference that variable subsequently anywhere in that set of statement, even in the absence of any explicit transactions.  Similarly, if I begin a transaction in one batch of statements, then declare a variable in a second batch, I cannot reference that variable in a third batch even though the transaction is still open.

Friday, August 1, 2014 - 4:03:16 AM - Daniel Back To Top (33971)

You can create non-clustered indexes on table variables too:

DECLARE @tab TABLE ( id int primary key nonclustered )

Wednesday, July 16, 2014 - 1:07:45 AM - Sushil Back To Top (32727)

Transaction logs are not recorded for the table-variable.

CREATE table #T (s varchar(128))
DECLARE @T table (s varchar(128))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @'
BEGIN transaction
  UPDATE #T set s='new value #'
  UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s             
---------------
old value #

s                
---------------
new value @

http://www.codeproject.com/Articles/18972/Temporary-Tables-vs-Table-Variables-and-Their-Effe

 

 

Saturday, September 28, 2013 - 12:14:08 PM - sandipG Back To Top (26982)

 

check below link for good example of temp table and table variable

 

http://sandipgsql.blogspot.in/2013/06/temp-table-and-temp-variable-in-sql.html

Friday, March 1, 2013 - 4:03:21 AM - ashish Back To Top (22498)

Is there any performance difference between Temp Table vs Table valued functions?

Wednesday, February 20, 2013 - 6:07:19 AM - Likhith shenoy Back To Top (22298)

Thank you so much Sir.. Ite really nice.. Keep writing.. 

Sunday, February 10, 2013 - 2:56:29 PM - Rick Back To Top (22040)

I just found a solution


SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW, ID FROM #TEMPGLBANKLIST

 

where #tempgl contains gl accounts in id field

 

Sunday, February 10, 2013 - 2:48:32 PM - Rick Back To Top (22039)

Hello - I just found this article - quite informative as I am reasonably new to SQL (previously used Visual Foxpro).  I do have a question about temp tables.  Is it possible to add a field with auto_increment functionality.  I need this for an application i am working on.  I tried playing with row numbers - got no where!

Monday, January 21, 2013 - 3:32:45 PM - Michael Feistel Back To Top (21602)

I stumbled across this very good article in my search to an explanation for a weird behaviour between two identical SQL servers (2008 R2, Dell PowereEdge 210R, 4GByte RAM). I run a view on which I group later and in total after grouping, there are never more than 30-50 rows max returned. That's why I've decided to do the post processing by means of storing the result (after grouped) in a table variable and do further processings with it.

However, the one server gives me a time out, the other one returns a result set within 2s. Before grouping, we're talking about 1500 - 3500 raw data rows. I'm running the grouping statement using the view with an insert transact on the table variable. Probably the wrong approach, ist it ?

 

1. VIEW

CREATE VIEW [dbo].[BookPerf]

AS

SELECT DISTINCT 

c.Jobnumber, 

s.[Description] AS Shift, 

c.ReelStand, 

c.Batch, 

c.DateStamp, 

c.bDate AS [Day], 

o.FirstName + ' ' + o.Surname AS UserName, 

c.oASC,

c.oDESC, 

t.MakeReadyDateTime AS mReady,

ISDATE(lm.Datestamp) AS LM, 

lm.Datestamp AS dLM,

NULLIF(COALESCE(lm.Username,c.Username),c.Username) AS uLM, 

t.ProductionStartDateTime AS pStart, 

ISDATE(fb.DateStamp) AS LS, 

fb.DateStamp AS dLS, 

ISDATE(db.DateStamp) AS DB, 

db.DateStamp AS dDB, 

t.ProductionFinishDateTime AS pEnd, 

ISDATE(lr.DateStamp) AS LR, 

lr.DateStamp AS dLR, 

COALESCE(lr.Username,c.Username) AS uLR

FROM ( 

SELECT 

Jobnumber, 

ReelStand, 

Batch, 

DateStamp, 

CAST(DateStamp AS DATE) AS bDate,

ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp ASC) AS oASC, 

ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp DESC) AS oDESC, 

ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY DateStamp ASC) AS cPiece, 

Username 

FROM StockControl.dbo.Consume

) c   

INNER JOIN StockControl.dbo.Batch AS b ON (b.Batch = c.Batch) 

INNER JOIN StockControl.dbo.Tickets t ON (c.Jobnumber = t.TicketID)

LEFT OUTER JOIN StockControl.dbo.Operator o ON c.Username = o.UserName 

LEFT OUTER JOIN StockControl.dbo.Shifts s ON t.EndShift = s.Shift

-- late start bookings

LEFT OUTER JOIN Consume fb ON

(fb.Jobnumber = c.Jobnumber) AND 

(fb.Batch = c.Batch) AND 

(c.oASC = 1) AND 

(fb.DateStamp > t.ProductionStartDateTime) 

-- late return bookings 

LEFT OUTER JOIN ( 

SELECT *, ROW_NUMBER() OVER (PARTITION BY Batch ORDER BY DateStamp ASC) AS rPiece 

FROM StockControl.dbo.[Return]) lr ON

(c.Batch = lr.Batch) AND 

(c.cPiece = lr.rPiece) AND 

(c.oDESC = 1) AND 

(lr.DateStamp > t.ProductionFinishDateTime) 

-- double bookings 

LEFT OUTER JOIN ( 

SELECT *, ROW_NUMBER() OVER (PARTITION BY Jobnumber, ReelStand ORDER BY DateStamp) AS oASC 

FROM StockControl.dbo.Consume) dB ON 

(c.oASC = db.oASC + 1) AND 

(c.DateStamp = db.DateStamp) AND 

(c.Jobnumber = db.Jobnumber) AND 

(c.ReelStand = db.ReelStand) 

-- late moves 

LEFT OUTER JOIN StockControl.dbo.[Move] lm ON 

(c.Batch = lm.Batch) AND (

(c.oASC = 1 AND lm.Datestamp BETWEEN t.MakeReadyDateTime AND c.DateStamp) 

OR 

(c.oASC > 1 AND DATEDIFF(mi,lm.Datestamp,c.DateStamp)<5) 

) 

WHERE 

(t.[Status] IN (3,4)) AND 

(t.GrossWeight > 1) 

GO

2. GROUPING AND POST PROCESSING
 
DECLARE @bookPerf TABLE (
gCol VARCHAR(30),
Reels INT,
LM INT,
pLM FLOAT,
sLM FLOAT,
LS INT,
pLS FLOAT,
sLS FLOAT,
DB INT,
pDB FLOAT,
sDB FLOAT,
LR INT,
pLR FLOAT,
sLR FLOAT,
pTotal FLOAT ,
sTotal FLOAT
)

INSERT @bookPerf (gCol, Reels, LM, pLM,LS, pLS, DB, pDB, LR, pLR, pTotal)
SELECT
Shift AS gCol,
NULLIF(COUNT(Batch),0) AS Reels,
NULLIF(SUM(LM),0) AS LM,
ROUND(100*(1-SUM(LM)/CAST(COUNT(Batch)+1e-6 AS FLOAT)),2) AS pLM,
NULLIF(SUM(LS),0) AS LS,
ROUND(100*(1-SUM(LS)/CAST(COUNT(Batch)+1e-6 AS FLOAT)),2) AS pLS,
NULLIF(SUM(DB),0) AS DB,
ROUND(100*(1-SUM(DB)/CAST(COUNT(Batch)+1e-6 AS FLOAT)),2) AS pDB,
NULLIF(SUM(LR),0) AS LR,
ROUND(100*(1-SUM(LR)/CAST(COUNT(Batch)+1e-6 AS FLOAT)),2) AS pLR,
ROUND(100*(1-SUM(LM + LS + DB + LR)/CAST(COUNT(Batch)+1e-6 AS FLOAT)),2) AS pTotal
FROM StockControl.dbo.BookPerf
WHERE
([Day] BETWEEN '2013-01-01' AND '2013-02-01') AND
('' = '' OR NOT (LM = 1 AND oASc > 1))
GROUP BY Shift
ORDER BY Shift

UPDATE @bookPerf SET
sLM = p.pLM,
sLS = p.pLS,
sDB = p.pDB,
sLR = p.pLR,
sTotal = p.pTotal
FROM (
SELECT
MIN(pLM) AS pLM,
MIN(pLS) AS pLS,
MIN(pDB) AS pDB,
MIN(pLR) AS pLR,
MIN(pTotal) AS pTotal
FROM @bookPerf) p

UPDATE @bookPerf SET
sLM = ISNULL((pLM - sLM)/(100-NULLIF(sLM,100)),1),
sLS = ISNULL((pLS - sLS)/(100-NULLIF(sLS,100)),1),
sDB = ISNULL((pDB - sDB)/(100-NULLIF(sDB,100)),1),
sLR = ISNULL((pLR - sLR)/(100-NULLIF(sLR,100)),1),
sTotal = ISNULL((pTotal - sTotal)/(100-NULLIF(sTotal,0)),1)

SELECT *,
ROUND(100*sLM,0) AS scale_LM,
ROUND(100*sLS,0) AS scale_LS,
ROUND(100*sDB,0) AS scale_DB,
ROUND(100*sLR,0) AS scale_LR,
ROUND(100*sTotal,0) AS scale_Total,
StockControl.dbo.hexColor(sLM) AS cLM,
StockControl.dbo.hexColor(sLS) AS cLS,
StockControl.dbo.hexColor(sDB) AS cDB,
StockControl.dbo.hexColor(sLR) AS cLR,
StockControl.dbo.hexColor(sTotal) AS cTotal
FROM @bookPerf
ORDER BY pTotal ASC

Saturday, January 19, 2013 - 10:19:52 AM - Rasik Kotadia Back To Top (21571)

Tim Ford is right on this, Constraint can be created during declartion of table variable but not after it has been declared.

Tuesday, December 4, 2012 - 9:20:32 AM - Jeff Moden Back To Top (20734)
  • You can not create constraints in table variables

Actually, you can with some severe limits.  You can declare a column to be UNIQUE at Table Variable creatio time.

Monday, April 9, 2012 - 8:39:04 AM - Prince Chawala Back To Top (16821)

thanks you

and good night

Thursday, March 1, 2012 - 6:51:57 AM - archana Back To Top (16227)

declare @temp table
(id int primary key,
name varchar(10) default getdate() )

"Yes I have tested and i  a Table Variable can have Constraint."

Thursday, March 31, 2011 - 10:12:15 AM - Muraleedharan Back To Top (13398)

Very good articile, easy to understand the differences. Keep writing

Wednesday, December 1, 2010 - 7:45:32 PM - Qingping Cheng Back To Top (10407)

Can someone explain what is differences between Temp tables and Physical tables? It is usually seen that some temp tables are created in a SP and then will be droped later in the same SP. If we need to run this SP prequently like once a day, should we create physical tables instead of cerating temp tables during SP running time? If we do so, does the SP run faster?

Sorry for this basic questions

Thanks

Friday, October 22, 2010 - 9:41:57 AM - HemaBhushan Back To Top (10293)

Dear sir, Recently I have gone through your article.  The same topic was given by the link is http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

According to you Both temp tables & table variables are logged in the transaction log. But according to the other site The first difference is that transaction logs are not recorded for the table variables. Can you please clarify which one to be considered. Thanks & Regards, HemaBhushan

Thursday, August 12, 2010 - 8:04:44 AM - jonmcrawford Back To Top (5954)

You can even index a table variable, if it's part of the primary key constraint, see http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx for more info.

 Followup after chatting with some folks, from Gail Shaw (blog):

"you can create primary key and unique constraints on a table variable, if they're done as part of the declaration. Alter table and create index statements fail."

eg:

declare @tbl table (
 id int identity primary key,
 Col1 varchar(20) unique,
 Col2 varchar(30),
 Col3 datetime,
 unique nonclustered (Col2, Col3, id)
)

And from Dave Ballentyne (blog):

"Remember that the optimizer uses statistics to decide to use an index. Table variables have none, it is assumed that they will have 1 row. So if you want to use an index you generally have to use a hint."

followup from Gail again "Unless the index is covering, in which case it will be used without a hint. "

Gotta love the SQL community :)

Sunday, August 10, 2008 - 5:24:34 AM - naveentnk Back To Top (1617)

<p>The below two statements are incorrect as specified by u. I have tested two points in sql server 2005 express edition by creating a table variable.

  • You can not create constraints in table variables
  • You can not create default values on table variable columns

i am able to create a primary key constraint for a table variable and i am able to set a default value for a column in table variable.

can u pls reply me for this comment. is this artical specific for sql server 2000? I didnt tried this in sql server 2000?

 

Waiting for ur reply.

 

 Thanks & regards

Naveen

<p>

Friday, August 8, 2008 - 12:55:02 PM - aprato Back To Top (1607)

 JDub

This is a great write up on the relationship between temp tables and recompiles

MSDN Blog 

Thursday, August 7, 2008 - 7:44:09 AM - Jdub Back To Top (1593)

I agree that when working with large data sets temp tables are the way to go.  I think it should be explained to readers that when working with small data sets that are changing there is a good chance that the temp table will decide to update statistics wich will force recompiles.  This can be a significant performance issue.

 Also I think it should be noted that temp table transactions are logged and so they we be rolled back.  There may be a scenario when jusers would not want tansactions rolled out if a procedure fails, maybe audit records for example.  In this case a table variable would be the way to go because its transactions are at the batch level only and would not be roll back.














get free sql tips
agree to terms