Table Variable in SQL Server

In this session i am going to explain you about Table Variable in SQL Server.

Introduction:

Table Variable introduced with SQL SERVER 2000 as an alternate for Temporary tables.

We can use Table Variable to store set of records temporally. Unlike other data type table varibale can not use as an input or output parameter like other data types. As we use variables in sql server to store some values with different data type in the same way we can use Table Variable for storing set of record that exists only for current scope. Table Variables are declared into the current batch or stored procedure with the Declare Statement

 

Here is the syntax for declaring Table Variable.

DECLARE @tablevariablename AS TABLE

(

Col1 datatype,

…..

Coln datatype

)

You can see the difference between declaring a table variable and creating a table.

Scope of Table Variable:

Table Variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exists after the scope of the Stored Procedure, batch or User-Defined function.

Performance:

Because of limited scope table variable uses fewer resources than temporary table. A temporary table inside stored procedure requires additional recompilation whereas the table variable does not require this additional recompilation of stored procedure.

Let’s start by creating a simple Employee table here is the script.

SET ANSI_NULLS ON

GO




SET QUOTED_IDENTIFIER ON

GO




SET ANSI_PADDING ON

GO




CREATE TABLE [dbo].[Employee](

       [ID] [int] NOT NULL,

       [Name] [varchar](100) NULL,

       [Address] [varchar](100) NULL,

       [ContactNo] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

       [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]




GO




SET ANSI_PADDING OFF

GO

 

 

 

Insert some dummy records here is the script:

INSERT INTO [dbo].[Employee]([ID],[Name],[Address],[ContactNo])VALUES(1,'Vinod Palne','Varanasi','9282899090');

INSERT INTO [dbo].[Employee]([ID],[Name],[Address],[ContactNo])VALUES(2,'Gaurav Mishra','Varanasi','9282899090');

You can see the Employee table after inserting record

 

In this example I am declaring a table variable and I will copy all the records from employee table.

Here is the script for declaring @tblEmployee.

DECLARE @tblEmploye AS TABLE

(

ID INT,

Name VARCHAR(100),

Address VARCHAR(100),

ContactNo VARCHAR(50)

)

Now we have to copy records from EmployeeTable to @tblEmployee. Here is the script:

DECLARE @tblEmploye AS TABLE

(

ID INT,

Name VARCHAR(100),

Address VARCHAR(100),

ContactNo VARCHAR(50)

)

INSERT INTO @tblEmploye (ID,Name,Address,ContactNo)

SELECT ID,Name,Address,ContactNo FROM Employee

SELECT * FROM @tblEmploye




Let’s perform an update operation on table variable




DECLARE @tblEmploye AS TABLE

(

ID INT,

Name VARCHAR(100),

Address VARCHAR(100),

ContactNo VARCHAR(50)

)

INSERT INTO @tblEmploye (ID,Name,Address,ContactNo)

SELECT ID,Name,Address,ContactNo FROM Employee

UPDATE @tblEmploye SET Name='Dheeraj Gupta' WHERE ID=1

SELECT * FROM @tblEmploye

Let’ perform delete operation on table variable

 

DECLARE @tblEmploye AS TABLE

(

ID INT,

Name VARCHAR(100),

Address VARCHAR(100),

ContactNo VARCHAR(50)

)

INSERT INTO @tblEmploye (ID,Name,Address,ContactNo)

SELECT ID,Name,Address,ContactNo FROM Employee

DELETE FROM @tblEmploye WHERE ID=1

SELECT * FROM @tblEmploye