Difference Between Table Variable and Temporary Table

Hello Friends Good Evening..Today I am going to talk about one more popular topic in SQL Server.
I have seen many developers who are not sure where to use table variable and where to use temporary table. After reading this article you will be able to answer yourself when to use table variable and when temporary table.

Lets see what are the differences first.

First Difference :- We cannot use table variables in transactions while we can use temporary table in transaction. In other words for table variables SQL Server doesn't log the (Insert,update, delete) operations into transaction log, so there is no concept of rollback and commit transactions for table variables.
Lets take an example to prove this.

CREATE TABLE #Temptbl(Col1 INT) -->Create table statement

Begin Tran--Beginning a transaction

INSERT INTO #Temptbl (Col1)
SELECT TOP 50 ROW_NUMBER() OVER(ORDER BYname)--> inserting rows in temporary table
FROM sys.sysobjects  

Rollback--> Rollback Transaction
Select * from #Temptbl-->Selecting rows from temporary table.

you can see that after rollback the transaction we are using select statement to see the result and it is coming as no result.It means whatever operation(Insert, Update,Delete) is made on the temporary table , it is logged on transaction log, that is why we are able to rollback or commit the transaction.

Now lets take another example to see what happens with table variables using transactions.

DECLARE @temp TABLE(Col1 INT)-->Create table variable
Begin Tran--Beginning a transaction

INSERT INTO @temp (Col1)
SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY name)--> inserting rows in table variable
FROM sys.sysobjects 
Rollback--> Rollback Transaction

select * from @temp-->Selecting rows from temporary variable.

Now you can see that after rollback the transaction we are using select statement to see the result and it will show all 50 records of the table those were inserted in the table variable for rollback the transaction.It means whatever operation(Insert, Update,Delete) is made on the table variable , that is not logged on transaction log, Hence we are able not able to rollback or commit the transaction.

Second Difference :- We cannot create non clustered indexes on table variables while we can create both type of indexes(Clustered and Non Clustered) on a temporary table.

Third Difference :- We need not to explicitly drop table variables as table variables are not physical objects in the database and the scope of the table variable is only to executed batch.
Where in temporary tables are physical objects stored in the database and are accessible within the session that created the temporary table.

Where to use table variable and where to use Temporary variables:- Table variables should be used where we need to work on smaller result set up to few thousands of records. When we are working on large result set we should prefer to use Temporary tables. Because we can optimize our queries by creating indexes on temporary tables while indexes are not supported by table variables.

Myth about Table variable and temporary table:- There is a myth around the storage of table variable that table variables are not stored in temp DB. Both table variable and temporary tables uses temporary database for storage. To prove this lets take an example.

CREATE TABLE #Temptbl (Col1 INT)-->Creation of temporary table

INSERT INTO #Temptbl (Col1)
SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY name)-->Inserting rows in temp table
FROM sys.sysobjects 

SELECT user_objects_alloc_page_count,database_id  
FROM sys.dm_db_session_space_usage

WHERE session_id = (SELECT @@SPID )--> dynamic management view used to see the space used by objects created by the current session

When I run the selected query in the management studio, the above result set shows page allocated count for the objects created by the current session. where database id 2 indicates Temp DB. Now I will run the below query and lets see if table variable is using the Temp DB or not.

In the prior result set user_objects_alloc_page_count value was 7 which has got increased(user_objects_alloc_page_count=11) after creating the table variable and inserting the data in the table variable. It clearly shows that temporary table and table variable both are stored in temp database.

I hope this article will help you to make a right decision whether to use table variable or temporary table and where to use which object.