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:
  1. Declare @<Variable_Name> TABLE(  
  2. Column_Name [Data_Type],  
  3. Column_Name [Data_Type],  
  4. Column_Name [Data_Type],  
  5. ......  
  6. )  
For Example
  1. Declare @TempTable TABLE(    
  2. id int,  
  3. Name varchar(20)    
  4. )    
 Table Variable
 
Now you can perform insert, update, delete and select all operations with it such as in the following, I write it like this:
  1. Declare @TempTable TABLE(      
  2. id int,    
  3. Name varchar(20)      
  4. )      
  5.   
  6. insert into @TempTable values(1,'Sourabh Somani')  
  7. insert into @TempTable values(2,'Shaili Dashora')  
  8. insert into @TempTable values(3,'Divya Sharma')  
  9. insert into @TempTable values(4,'Swati Soni')  
  10.   
  11. Select * from @TempTable  
select
 
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:

    temporary tables and temporary variables

  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:
    1. Declare @TempTable TABLE(      
    2. id int,    
    3. Name varchar(20)      
    4. )      
    5. begin tran T  
    6. insert into @TempTable values(1,'Sourabh Somani')  
    7. insert into @TempTable values(2,'Shaili Dashora')  
    8. insert into @TempTable values(3,'Divya Sharma')  
    9. insert into @TempTable values(4,'Swati Soni')  
    10. commit tran T  
    11. Select * from @TempTable  
    Transaction

    or 
    1. Declare @TempTable TABLE(      
    2. id int,    
    3. Name varchar(20)      
    4. )      
    5. begin tran T  
    6. insert into @TempTable values(1,'Sourabh Somani')  
    7. insert into @TempTable values(2,'Shaili Dashora')  
    8. insert into @TempTable values(3,'Divya Sharma')  
    9. insert into @TempTable values(4,'Swati Soni')  
    10. rollback tran T  
    11. Select * from @TempTable  
    Transaction operation
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.

Similar Articles
sourabhsomani.com
sourabhsomani.com