SQL Server-Table Variable

Table variable:- SQL Server provides an variable known as table variable which is used to store data in a similar way as we store data in physical tables but with some limitations. Like other SQL variable, it is also declare with the help of the Declare keyword with @ prefix. The Syntax of declaring a table variable is given below:-
Declare @tablename table(col1 datatype, col2 datatype, col3 datatype……..coln datatype)

Suppose we want to declare a table variable named @tbl3, the we will use the following syntax:

Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

Also to insert data into the table variable , we can use the insert command similar to physical, and temporary tables
Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( ‘Vivek’, ‘ Johari’, getdate())

Scope of the table variable is limited to the block of code in which it is defined or within the stored procedure in which it is defined. Unlike temporary tables (local or global) table variable it is not accessible in the procedures executed within the procedure in which it is defined. Like other variables, table variable is also created on the memory. Also since table variable is itself a variable, we doesn’t need to force it deletion through the use of Drop statement.
Example of using a table variable inside a stored procedure.

Create  procedure test_tablvariable
as
begin
  Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( ‘Vivek’, ‘ Johari’, getdate())
  select * from @employee
end 

Unlike local and global temporary tables, we can’t apply transaction on the table variable.

Alter procedure test_tablvariable
as
begin
  begin tran
  Declare @employee table (id int identity(1,1), empFname nvarchar(100),  empEname nvarchar(100), empdate datetime)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( ‘Vivek’, ‘ Johari’, getdate())
   Rollback
  select * from @employee
end

 The execution of the above store procedure will give the following result set in spite of the Rollback statement written above the select command.

Also unlike temporary tables and physical tables, we can’t add constraints on it. For example, if we try to add primary key on the table @employee, it will throw an error.

Alter Procedure test_tablvariable
As
Begin
  Declare @employee table (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  alter table @employee add constraint pk_temp primary key (id)
  Insert into  @employee  ( empFname  ,  empEname  , empdate )
  Values ( ‘Vivek’, ‘ Johari’, getdate())
  select * from @employee
End

The execution of the above procedure will give the following error.

Similarly we also can’t define indexes on the table variable.

Table variable is good when we need to store less number of  rows. But if the size of the data or number of rows keeps on  increasing  then storing the data into the table variable will not going to be a good idea. Since we can’t use temporary tables inside the SQL Functions, table variable can be very useful to store temporary data and return the data in the table format.