Table Variable in SQL Server

Introduction

 

In my previous article I described Temporary Tables in SQL. So there is one alternative approach also available to do the same thing as a temporary table. The alternative to a temporary table is a "Table Variable". We can perform similar operations using Table Variables but there is a difference that we will see further in this article.

 

Table Variable

 

A Table Variable is a variable that can store the complete table of the data inside it. It is similar to a Table Variable but as I said a Table Variable is a variable. So how do we declare a variable in SQL? Using the @ symbol. The same is true for a Table Variable. so the syntax of the Table Variable is as follows:

Declare @<Variable_Name> TABLE(
Column_Name [Data_Type],
Column_Name [Data_Type],
Column_Name [Data_Type],
......
)

For Example

Declare @TempTable TABLE(
id int,
Name varchar(20)
)

 

 
Now you can perform insert, update, delete and select all operations with it such as in the following, I write it like this:

Declare @TempTable TABLE(
id int,
Name varchar(20)
)

insert into @TempTable values(1,'Sourabh Somani')
insert into @TempTable values(2,'Shaili Dashora')
insert into @TempTable values(3,'Divya Sharma')
insert into @TempTable values(4,'Swati Soni')

Select * from @TempTable

 

Difference between temporary tables and Table Variable

 

There are a difference between temporary tables and temporary variables, it is:

1. A Table Variable is not available after execution of the complete query so you cannot run a single query but a temporary table is available after executing the query.
   
   For example:
   
   
   
   
2. A Transaction (Commit and Rollback) operation is not possible in a Table Variable but in a temporary table we can perform transactiona (Commit and Rollback).
   
   For example:
   

   Declare @TempTable TABLE(
   id int,
   Name varchar(20)
   )
   begin tran T
   insert into @TempTable values(1,'Sourabh Somani')
   insert into @TempTable values(2,'Shaili Dashora')
   insert into @TempTable values(3,'Divya Sharma')
   insert into @TempTable values(4,'Swati Soni')
   commit tran T
   Select * from @TempTable

   
   
   or 
   

   Declare @TempTable TABLE(
   id int,
   Name varchar(20)
   )
   begin tran T
   insert into @TempTable values(1,'Sourabh Somani')
   insert into @TempTable values(2,'Shaili Dashora')
   insert into @TempTable values(3,'Divya Sharma')
   insert into @TempTable values(4,'Swati Soni')
   rollback tran T
   Select * from @TempTable

   
   

Important Points about Table Variables

• The same as a temporary table.
• Single query cannot be executed.
• When we want to perform a few operations then use a Table Variable otherwise if it is a huge amount of data operation then use a temporary table.
• Commit and Rollback (Transaction) cannot be possible with Table Variables so if you want to perform a transaction operation then always go with temporary tables.