Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Differences between SQL Server temporary tables and table variables

MSSQLTips author Tim Ford By:   |   Read Comments (19)   |   Related Tips: More > T-SQL

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

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_idColorCodeModelNameCodeDateEntered)
VALUES (1,'BlueGreen''Austen'200801GETDATE())

SELECT Car_idColorCodeModelNameCodeDateEntered FROM dbo.#Cars

DROP TABLE dbo.[#Cars]

This returns the following results:

--Table Variable:
DECLARE @Cars TABLE
   
(
   
Car_id 
int NOT NULL, 
   
ColorCode 
varchar(10), 
   
ModelName 
varchar(20), 
   
Code 
int ,
   
DateEntered datetime
   
)
   
INSERT INTO @Cars (Car_idColorCodeModelNameCodeDateEntered)
VALUES (1,'BlueGreen''Austen'200801GETDATE())

SELECT Car_idColorCodeModelNameCodeDateEntered 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:

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

Msg 1087, Level 15State 2Line 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')

SELECT FROM dbo.usp_customersbyPostalCode('05033')

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?


Last Update: 8/6/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, August 07, 2008 - 7:44:09 AM - Jdub Read The Tip

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.


Friday, August 08, 2008 - 12:55:02 PM - aprato Read The Tip

 JDub

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

MSDN Blog 



Sunday, August 10, 2008 - 5:24:34 AM - naveentnk Read The Tip

<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>


Thursday, August 12, 2010 - 8:04:44 AM - jonmcrawford Read The Tip

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 :)

Friday, October 22, 2010 - 9:41:57 AM - HemaBhushan Read The Tip

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


Wednesday, December 01, 2010 - 7:45:32 PM - Qingping Cheng Read The Tip

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


Thursday, March 31, 2011 - 10:12:15 AM - Muraleedharan Read The Tip

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


Thursday, March 01, 2012 - 6:51:57 AM - archana Read The Tip

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

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


Monday, April 09, 2012 - 8:39:04 AM - Prince Chawala Read The Tip

thanks you

and good night


Tuesday, December 04, 2012 - 9:20:32 AM - Jeff Moden Read The Tip
  • 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.


Saturday, January 19, 2013 - 10:19:52 AM - Rasik Kotadia Read The Tip

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


Monday, January 21, 2013 - 3:32:45 PM - Michael Feistel Read The Tip

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

Sunday, February 10, 2013 - 2:48:32 PM - Rick Read The Tip

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!


Sunday, February 10, 2013 - 2:56:29 PM - Rick Read The Tip

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

 


Wednesday, February 20, 2013 - 6:07:19 AM - Likhith shenoy Read The Tip

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


Friday, March 01, 2013 - 4:03:21 AM - ashish Read The Tip

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


Saturday, September 28, 2013 - 12:14:08 PM - sandipG Read The Tip

 

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


Wednesday, July 16, 2014 - 1:07:45 AM - Sushil Read The Tip

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

 

 


Friday, August 01, 2014 - 4:03:16 AM - Daniel Read The Tip

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

DECLARE @tab TABLE ( id int primary key nonclustered )



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.