Table Variable in SQL server 2008

This article demonstrates how to use Table variables rather using temporary tables in SQL Server.
  • 1713

Introduction

Table Variable is used like a variable in which we store data that we store in a temporary tables. In table variable, we declare a variable of type table. This is a alternative of temporary tables in which we store records.

Creating Table Variable

Declare @student Table
(
      RollNum int,
      Name varchar (15),
      City varchar (15)
)

This declares a table variable named @student that we can use in place of temporary table. It behaves just like regular table.

Insert values into Table Variable

Declare @student Table
(
     RollNum int,
     Name varchar (15),
     City varchar (15)
)
insert into @student (RollNum, Name, City) values (1, 'rahul', 'ghaziabad')
select * from @student

Now run whole code defined above and see output. Individual insert statement or any other statement will not work so whole code must run at a time. It woks like a table.

OUTPUT

TableVariable.jpg

Table variable is much faster than Temporary Table, as when we create temporary table, which physically created in tempdb database but when we create a table variable, it is created in memory. So it is much faster than temporary table.


© 2013 dotNetheaven. All rights reserved.