Site Loader
Get a Quote

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 recordTable Variable

 

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

 

 

Post Author: TechieFlair

Table Variable in SQL Server: In this tutorial, 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, a table variable cannot use as an input or output parameter like other data types. As we use variables in SQL server to store some values with the different data type in the same way we can use Table Variable for storing the set of record that exists only for the current scope. Table Variables are declared into the current batch or stored procedure with the Declare Statement.

Table Variable in SQL Server

Here is the syntax for declaring Table Variable.

DECLARE @tablevariablename AS TABLE

(

Col1 datatype,

…..

Colndatatype

)

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

Scope:

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 exist after the scope of the Stored Procedure, batch or User-Defined function.

Performance:

Because of limited scope, it uses fewer resources than a 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.

SETANSI_NULLSON

GO




SETQUOTED_IDENTIFIERON

GO




SETANSI_PADDINGON

GO




CREATETABLE[dbo].[Employee](

       [ID] [int]NOTNULL,

       [Name] [varchar](100)NULL,

       [Address] [varchar](100)NULL,

       [ContactNo] [varchar](50)NULL,

PRIMARYKEYCLUSTERED

(

       [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




SETANSI_PADDINGOFF

GO


Insert some dummy records here is the script:
INSERTINTO[dbo].[Employee]([ID],[Name],[Address],[ContactNo])VALUES(1,'Vinod Palne','Varanasi','9282899090');

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

You can see the result of Employee Table

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@tblEmployeASTABLE

(

IDINT,

NameVARCHAR(100),

AddressVARCHAR(100),

ContactNoVARCHAR(50)

)

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

DECLARE@tblEmployeASTABLE

(

ID INT,

Name VARCHAR(100),

Address VARCHAR(100),

ContactNo VARCHAR(50)

)

INSERTINTO @tblEmploye(ID,Name,Address,ContactNo)

SELECT ID,Name,Address,ContactNo FROM Employee

SELECT *FROM @tblEmploye

Table Variable in SQL Server

Let’s perform an update operation:
DECLARE@tblEmployeASTABLE

(

IDINT,

NameVARCHAR(100),

AddressVARCHAR(100),

ContactNoVARCHAR(50)

)

INSERTINTO@tblEmploye(ID,Name,Address,ContactNo)

SELECTID,Name,Address,ContactNoFROMEmployee

UPDATE@tblEmployeSETName='Dheeraj Gupta'WHEREID=1

SELECT*FROM@tblEmploye

Table Variable

Let’ perform the delete operation:
DECLARE@tblEmployeASTABLE

(

IDINT,

NameVARCHAR(100),

AddressVARCHAR(100),

ContactNoVARCHAR(50)

)

INSERTINTO@tblEmploye(ID,Name,Address,ContactNo)

SELECTID,Name,Address,ContactNoFROMEmployee

DELETEFROM@tblEmployeWHEREID=1

SELECT*FROM@tblEmploye


Table Variable

Hope you like this tutorial.

Thank You

Post Author: TechieFlair

4 Replies to “Table Variable in SQL Server”

  1. Very good article! We are linking to this particularly great content on our site. Keep up the great writing.

Leave a Reply

Your email address will not be published.