Reader Level:
ARTICLE

Using Table Variables Instead of Temporary Tables in SQL Server

Posted by Karthika Palaniswamy Articles | SQL Server October 23, 2009
This article demonstrates how to use Table variables in SQL Server instead of temporary tables.
  • 0
  • 0
  • 10597

Microsoft introduced table variables in SQL Server. Table variables are used instead of temporary tables. Similar to temp tables, we can use table variables to store data that we used to store in temp tables.

The following statement is used to declare a Table variable, which is pretty similar to a CREATE TABLE statement in SQL.

Example:

Declare @customersvar  Table( Id int  identity(1,1), 
customerID   nchar(5)  NOTNULL, Name   varchar(50) ,
Address  varchar(max) , PhoneNo  varchar(50) )

We can write the following INSERT INTO statement to insert values in the table variable.

Insert into @customervar Table (customerID, Name, Address,  PhoneNo )

We can write the following SELECT statement to populate the table variable.

 SELECT * FROM @customersvar  

And to populate the table variables first fifty values, you can write the following SELECT statement

 SELECT TOP 50 * FROM @customersvar  

When we create a temporary table (#TABLE) , which  physically creates  the table in tempdb so it is creates  burden . When we create a table variable which is creating  in memory so it's much faster.

And we can use table variables when creating batches, stored procedures, and user-defined functions (UDFs).

And also you can UPDATE records in your table variable as well as DELETE records.

Example:

UPDATE  @customersvar  SET Name  = 'Reema' WHERE  customerID = 158
DELETE  FROM  @customersvar   WHERE  customerID = 1020

Article Extensions
Contents added by A Pragmatist on Oct 24, 2009
there are still yet a few things that table variables cannot do, such as be output for stored procedures.. E.G.: Insert Into Exec... can not create indexes explicitly... and if you do some profiling you will see both table variables and # variables get allocated in tempdb... main advantage appears to be built in housekeeping of table variables...
COMMENT USING

Trending up