Compare a temporary table and a table variable

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

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

Select all Open in new window

 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
                      

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

Select all 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)
                       )
                      

Select all 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)
                      

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

Select all 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)
                      

Select all Open in new window

Information statistics


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)
                      

Select all Open in new window

Information of statistics changed

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; 
                      

Select all Open in new window

If we want SQL Engine to optimize @V, use OPTION(RECOMPLIE)

SELECT * FROM @tblEmployee OPTION(RECOMPILE)
                      

Select all Open in new window

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
                      

Select all Open in new window

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)
                      

Select all Open in new window

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
                      

Select all 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.