Using Table Variables Instead of Temporary Tables in SQL Server

This article demonstrates how to use Table variables in SQL Server instead of temporary tables.

Karthika Palaniswamy

Oct 23, 2009 · Updated May 20, 20121 min read20.6k40100Article

SQL Server Stored ProceduresTable VariablesUser-Defined Functions UDFsbatches

4 Comments

Join the conversation! Your thoughts help the community grow.

Dennis Thomas8y ago
Good Attempt Karthika, it's short and crisp! But it is a myth that table variables are stored in memory. Both temporary table and temp variable are stored in tempdb.
- Dennis Thomas8y ago
  A correction to my above comment. Karthika is partially correct about table variable that it is stored in memory. But it always not, under memory pressure, some pages that may include the one belonging to a table variable can be pushed to tempdb. Because of this fact, when some people do tests to check if it the table variable is stored in tempdb may get a positive result.
Lalit Raghuvanshi11y ago
There is another detailed article on table variable: Detailed examples on Declaring and using Table Variables in SQL SERVERhttp://www.webcodeexpert.com/2015/01/example-to-declare-and-use-table.html
- Dennis Thomas8y ago
  You shared a nice link Lalit!

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:

SQL

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.

Code

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

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

Code

 SELECT * FROM @customersvar

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

Code

 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:

SQL

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