There is no any thumb rule to decide whether to use Table Variable or Temporary Table. It depends upon the requirement of the application and different situations.
Following are some of the facts for Table Variable and Temporary Table:
(1) Once the structure of a Table Variable is defined with DECLARE statement, it can not be altered. The structure of a Temporary Table can be altered once it is defined with CREATE TABLE statement.
(2) We can not create any index explicitly on Table Variables. Only indexes for Primary Key and Unique Key can be available on Table Variables. On the other hand, indexes can be created on Temporary Tables.
(3) Transactions and related statements like ROLLBACK TRANSACTION can not affect Table Variables. That is to say, if some DML operations are performed on a Table Variable within a transaction followed by the execution of ROLLBACK TRANSACTION, then the DML operations performed on Table Variable will not be rolled back. Transactions do affect the temporary tables and DML operations performed on Temporary Table can either be committed or rolled back.
(4) STATISTICS are not maintained for Table Variables. STATISTICS can not be created on Table Variable with CREATE STATISTICS statement. While, STATISTICS can be maintained and created for Temporary Tables.
(5) Table Variable can not be used as target table in SELECT…INTO statement. Temporary Table can be used as target table in SELECT…INTO statement.
(6) Table Variable declared outside dynamic SQL statement is not accessible in dynamic SQL statements executed either by spexecutesql or EXECUTE statement. Temporary Table declared outside dynamic SQL statement is accessible in dynamic SQL statements executed either by spexecutesql or EXECUTE statement.
(7) Table Variable is not accessible to a nested child stored procedure. A Temporary Table is accessible to a nested child stored procedure.
There is one common thing between Table Variable and Temporary Table is that they both use TempDB database. There is a kind of understanding amongst the developers that Table Variable is an in-memory object and thus is faster than Temporary Table. This is not entirely true. If the data which is to be stored and processed in a Table Variable is large and can not fit into memory, then Table Variable may use tempdb database.
To prove this point, we can consider following example. In this example, we are creating a table with 5 million records. Then, a stored procedure is created which fetches data from this table and inserts this data into Table Variable followed by a simple SELECT statement on this Table Variable.
CREATE TABLE tblData
ID INT IDENTITY(1,1)
--Insert 5 Million records
INSERT INTO tblData(SomeData) VALUES (CHAR(RAND()*100))
Now, we are creating following stored procedure
CREATE PROCEDURE spTestTableVar
DECLARE @TempTable TABLE
INSERT INTO @TempTable
SELECT ID,SomeData FROM tblData
SELECT * FROM @TempTable
When you call this stored procedure, during the execution of stored procedure, execute the following SELECT statement in another query window:
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.dm_db_file_space_usage
In result set of query sys.dmtranlocks, you will notice that there are few exclusive locks at page level for resourcedatabaseid=2 by the session that executed the stored procedure. To identify the session, look for column requestsessionid. The resourcedatabaseid=2 means tempdb database.
The DMV sys.dmdbfilespaceusage gives page allocation details for tempdb database. During the execution of above stored procedure, you will notice that the value for column userobjectreservedpagecount is updated which reveals the total number of pages allocated to user objects.
This means that Table Variable stores its data in tempdb database.
I have also created the “Temporary Table” version of this stored procedure as shown below:
CREATE PROCEDURE spTestTableTemp
CREATE TABLE #TempTable
INSERT INTO #TempTable
SELECT ID,SomeData FROM tblData
SELECT * FROM #TempTable
While playing around these stored procedures, I have noticed that if a clustered index is created on ID column of #TempTable before inserting data, then it boosts up performance a little bit! I compared the executions of both of these stored procedures and looked upon the counts for CPU, Duration, Read and Write in SQL Profiler but did not find much difference except of a few milliseconds. This may be because this is a simple example. However, in case stored procedure is going to be lengthy one and the data you are going to store in either Table Variable or Temporary Table is going to be large and queried frequently on the basis of by filtering few columns upon which the indexes are desirable then Temporary Tables can outperform Table Variables. Because, on Table Variable we can not have indexes other than for Primary Key and Unique Key. This means that we can not have index on Table Variable on non-unique columns. So, data retrievals from large datasets which essentially require helpful indexes can be poor in case of Table Variables. On the contrary, we can create Clustered/Nonclustered indexes on required columns on Temporary Tables. Also, STATISTICS are maintained for Temporary Tables which can be used efficiently by query optimizer in order to generate better execution plans for the queries.
However, there is one benefit of using Table Variables over Temporary Table is that Table Variables cause fewer re-compilations than Temporary Tables because they are treated as variables and not as objects; thus not requiring the re-resolution phase that Temporary Tables require. As Temporary Tables are objects themselves, stored procedures that make use of Temporary Tables tend to be re-complied frequently due to re-resolution phase for non-existing objects (Temporary Tables) in order to generate execution plans. These frequent re-compilations can degrade and hurt the query performance.
Looking at the list of restrictions/limitations of Table Variables as compared to Temporary Tables, one has so many reasons to use Temporary Tables instead of Table Variables if the cost of re-compilations is not higher than the cost of poor data retrievals with Table Variables due to the lack of appropriate indexes and statistics on large data.
To sum up the things, one can come on rough conclusion that Table Variables are suitable for small datasets while Temporary Tables are suitable for large datasets. However, to any problem solution, it is always desirable to implement the solution with these two different scenarios; either with Table Variable or Temporary Table and make the decision by comparing the performance and the required functionalities achieved by each solution.