What is Table Variable

Table Variable:

A Table Variable is a variable that can store the complete table of the data inside.

Syntax:

1. Declare @<Variable_Name> TABLE(  
2. Column_Name [Data_Type],  
3. Column_Name [Data_Type],  
4. Column_Name [Data_Type],  
5. ......  
6. )  

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  

Difference between temporary tables and Table Variable

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. 

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).

⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

⇒ Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.

⇒ Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.

⇒ Temporary table allows Schema modifications unlike Table variables.

Points to Remember:

Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.

Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.