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 in SQL Server

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 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 is a difference between temporary tables and temporary variables, it is:

1. A Table Variable is not available after the execution of the complete query so you cannot run a single query but a temporary table is available after executing the query.

   Example

2. A Transaction (Commit and Rollback) operation is not possible in a Table Variable but in a temporary table, we can perform transactions (Commit and Rollback).

   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.
• A 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.