<

Compare a temporary table and a table variable

Published on
4,352 Points
1,152 Views
2 Endorsements
Last Modified:
The debate between temporary table and variable table is an old on but sometimes people ask about using temporary tables and variable tables to store temporary and what is better. What are the disadvantages and advantages of each?

Today, I would like to share with you about differences between temporary table and variable table. As a rule, local temporary table begins with # character and variable table begins with @. In this article, #T stands for a local temporary table and @V stands for a variable table.

The purpose of #T is to store temporary data and you can use them again in your batch code or your transaction. @V also stores temporary data. It was used to avoid recompiling your batch code or your store procedure as the first purpose in SQL 2000. After that It usually was used by SQL Developer / DBA and became one of popular ways to store temporary data. The debate was begun from that. 

#T likes a base table so it contains all properties of a base table except it will be deleted afer your batch code executing.  OK, let's go to investigate the differences.

1 - Statement to create #T and @V.
 
USE AdventureWorks2012
GO
-- Create temporary table
CREATE TABLE #T (Col1 varchar(128))

GO
-- Create variable table
CREATE @V TABLE (Col1 varchar(128))

Open in new window


Some people think that #T will be created in TempDB database and @V will be created in memory. This isn't the case. After creating them, query from TempDB database
 
USE AdventureWorks2012
GO
CREATE TABLE #T (Col1 varchar(128))
--Below query lists all the Tables in TempDB
SELECT * From TEMPDB.sys.tables
GO
--Declare a Table Variable
DECLARE @V TABLE(Col1 varchar(128))
--Verify whether Table Variable is created in TempDB
SELECT * From TEMPDB.sys.tables
GO

Open in new window

Storage.PNGStorage.PNG 2-We can roll-back temporary table but we can not do it with variable table.

USE AdventureWorks2012
GO
CREATE TABLE #T (Col1 varchar(128)) 
INSERT INTO #T SELECT 'A'
BEGIN TRANSACTION
UPDATE #T SET Col1 ='B'
ROLLBACK TRANSACTION
SELECT * FROM #T 
DROP TABLE #T

Open in new window

Above scripts, we see that after we roll-back transaction, value of Col1 will return A. Continue with @V

USE AdventureWorks2012
GO
BEGIN TRANSACTION	
DECLARE @V table (Col1 varchar(128)) 
INSERT INTO @V SELECT 'B'     
UPDATE @V SET Col1='D' 
ROLLBACK TRANSACTION
SELECT * FROM @V 

Open in new window

Obviously, value of Col1 is D, we could not roll-back transaction within @V.

3- We can create clustered-indexes, non-clustered indexes on #T but only PRIMARY KEY and UNIQUE CONSTRAINT for @V. We want create non-clustered index on @V, this could not.

USE AdventureWorks2012
GO
 DECLARE @Customer TABLE
 (
 CustomerID INT PRIMARY KEY CLUSTERED,
 Name varchar(50) UNIQUE-- CLUSTERED(Name)
 )

Open in new window


4-We could not apply DML statements on @V

DECLARE @V TABLE (ID int)
ALTER TABLE @V ADD Col1 varchar(128)

Open in new window



Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@V'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Col1'.
5-@V can be used in User Defined Function, pass variable table to store procedure. 

USE AdventureWorks2012
GO
CREATE FUNCTION fn_FunctionWithVariableTable()
RETURNS INT
AS
BEGIN
	DECLARE @V TABLE (ID int)
	IF EXISTS(SELECT 1 FROM @V)
		RETURN 1
	RETURN 0
END
GO
DROP FUNCTION fn_FunctionWithVariableTable

GO
CREATE FUNCTION fn_FunctionWithTemporaryTable()
RETURNS INT
AS
BEGIN
	CREATE TABLE #T(ID int)

	IF EXISTS(SELECT 1 FROM #T)
		RETURN 1
	RETURN 0
END
GO
DROP FUNCTION fn_FunctionWithTemporaryTable

Open in new window

When you create function with #T, error will be raised.


Msg 2772, Level 16, State 1, Procedure fn_FunctionWithTemporaryTable, Line 5
Cannot access temporary

6- We can update STATISTIC on #T but could not on @V.
Create temp table #Customer with clustered index, insert data and see information of statistics
 

USE AdventureWorks2012
 GO
CREATE TABLE #Employee ([BusinessEntityID] INT,[NationalIDNumber] nvarchar(15))
INSERT INTO #Employee
SELECT BusinessEntityID,NationalIDNumber
FROM HumanResources.Employee

CREATE CLUSTERED INDEX idx_BusinessEntityID ON #Employee([BusinessEntityID])

DBCC SHOW_STATISTICS('tempdb..#Employee',idx_BusinessEntityID)

Open in new window


Information statistics
Statistics1.png

Next, insert more data into temp table and see statistics again
 

INSERT INTO #Employee
VALUES(291,'A'),(292,'B'),(293,'C')

UPDATE STATISTICS #Employee

DBCC SHOW_STATISTICS('tempdb..#Employee',idx_BusinessEntityID)

Open in new window

Information of statistics changed
Statistics2.PNG
However, we can not apply UPDATE STATISTICS on @V and by default SQL Engine understand estimate rows =1
SET NOCOUNT ON;

DECLARE @tblEmployee TABLE ( 
    [NationalIDNumber] [nvarchar](15) NOT NULL, 
    [LoginID] [nvarchar](256) NOT NULL, 
    [OrganizationNode] [hierarchyid] NULL, 
    [JobTitle] [nvarchar](50) NOT NULL, 
    [BirthDate] [date] NOT NULL, 
    [MaritalStatus] [nchar](1) NOT NULL, 
    [Gender] [nchar](1) NOT NULL, 
    [HireDate] [date] NOT NULL, 
    [SalariedFlag] bit NOT NULL, 
    [VacationHours] [smallint] NOT NULL, 
    [SickLeaveHours] [smallint] NOT NULL);

INSERT INTO @tblEmployee 
SELECT [NationalIDNumber] 
      ,[LoginID] 
      ,[OrganizationNode] 
      ,[JobTitle] 
      ,[BirthDate] 
      ,[MaritalStatus] 
      ,[Gender] 
      ,[HireDate] 
      ,[SalariedFlag] 
      ,[VacationHours] 
      ,[SickLeaveHours] 
FROM AdventureWorks2012.[HumanResources].[Employee];

SET STATISTICS PROFILE ON;

SELECT * FROM @tblEmployee

SET STATISTICS PROFILE OFF; 

Open in new window

Statistic3.PNG
If we want SQL Engine to optimize @V, use OPTION(RECOMPLIE)
SELECT * FROM @tblEmployee OPTION(RECOMPILE)

Open in new window

Statistics4.PNG
Obviously, SQL Engines provides more correct estimation, value is 290 instead 1.  
With above comparison, STATISTICS is one of facters we should consider to determine #T or @V in case we have to process large amount of data.

7-PARALLELISM
We could apply OPTION( MAXDOP ) on #T in case your environment has many processor to improve performance 
USE AdventureWorks2012
 GO
CREATE TABLE #Sales ([SalesOrderID] INT,[ProductID] INT,[ModifiedDate] DATE)
INSERT INTO #Sales
SELECT SalesOrderID,ProductID,ModifiedDate
FROM Sales.SalesOrderDetail
SELECT *
FROM #Sales p
ORDER BY [ProductID]  ASC
OPTION( MAXDOP 2)

DROP TABLE #Sales

Open in new window

Parallelism1.png
Our execution plan used Parallelism mechanism. But what will happen with @V
 
USE AdventureWorks2012
 GO
DECLARE @Sales TABLE ([SalesOrderID] INT,[ProductID] INT,[ModifiedDate] DATE)
INSERT INTO @Sales
SELECT SalesOrderID,ProductID,ModifiedDate
FROM Sales.SalesOrderDetail
SELECT *
FROM @Sales p
ORDER BY [ProductID]  ASC
OPTION( MAXDOP 2)

Open in new window

Parallelism2.PNG
The difference here, SQL Engine does not use Parallelism mechanism as our expectation.

8- Using Scope
#T exists within session user while @V is limited to store procedure or batch which it is called
USE AdventureWorks2012
GO
CREATE TABLE #T (Col1 varchar(128)) 
INSERT INTO #T SELECT 'A'
GO
SELECT * FROM #T
GO
DECLARE @V table (Col1 varchar(128)) 
INSERT INTO @V SELECT 'B'
GO
SELECT * FROM @V

Open in new window

After GO, we could still query data from #T but error will happen if we query @V


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@V".
I have just  provide to you some differences between #T and @V. In generall, you could use both to store a small data. However, you must consider if your data is large ( about >10.000 rows) because #T has properties to help run better such indexes, parallelism mechanism, statistics,...

The comparasion on 2008R2 but there are improvements of @V with higher versions (2012...). Because @V is gaining in popularly, Microsoft provides more properties for @V and I will talk about them in next article.
2
Comment
Author:Dung Dinh
1 Comment
LVL 66

Expert Comment

by:Jim Horn
Very well written and illustrated.  Voting Yes.

>English is not my native language.
You're one of the better 'English as a Second Language' authors compared to many we see around here..
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month