Table Variable in SQL server 2008
This article demonstrates how to use Table variables rather using temporary tables in SQL Server.
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
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.