Using TABLE Variables

By Bill Graziano on 7 June 2002 | Tags: Table Design


Srinivas R writes "hi all, How do i use table data type and what is the use ??? Let me know with a good sample. Wallops!!!!"

Wallops? What does that mean? Is that good? I hope so! Just using the word "Wallops" is haf the reason your question got answered!

Table variables are just plain cool. If you're using temporary tables and don't need transactions on those tables and want better performance I'd strongly consider using table variables instead. Table variables were introduced in SQL Server 2000.

You create a table variable the same way you create any other variable: using the declare statement:

declare @TableVar table (CustomerID nchar(5) NOT NULL)

This declares a table variable named @TableVar that we can use in place of a temporary table. You can use a table variable for just about anything you'd use a regular table. The following statements won't work however:

INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable

The only constraints you can use are PRIMARY KEY, UNIQUE KEY, and NULL. The following script will work just fine in Northwind:

declare @TableVar table ( 
	NewPK int identity(1,1), 
	CustomerID nchar(5) NOT NULL  )

Insert Into @TableVar (CustomerID)
Select	CustomerID
From	Customers

Select	Top 5 *
from	@TableVar

I don't know if that's quite the "good sample" you were looking for but table variables are pretty simple to use. When you create a temporary table (#TABLE) it physically creates the table in tempdb. This creates overhead. When you create a table variable it only resides in memory which means it's much faster. A table variable goes out of scope immediate after the batch ends -- just like regular variables. This means you don't have to explicitly drop them at the end of scripts.

Using table variables also reduces the recompilations of your code. Also because they aren't physically written to the disk they aren't impacted by transaction rollbacks. I'd definitely test any use inside a transaction to make sure you're getting the expected results.

That's about it for table variables. SQLTeam was originally written in SQL Server 7. As I make changes to the code I'm gradually replacing all the temporary table with table variables without any problems. Good luck!


Related Articles

Implementing Table Interfaces (19 May 2008)

Implementing Table Inheritance in SQL Server (20 February 2008)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

The Case for the Surrogate Key (9 August 2002)

More Trees & Hierarchies in SQL (1 May 2002)

Default Constraint Names (24 January 2001)

Temporary Tables (17 January 2001)

Denormalize for Performance (10 January 2001)

Other Recent Forum Posts

SQL Server to PostgreSQL (24m)

SQL Union Create Custom Field to Different 2 Tables Output (5h)

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (12h)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (12h)

Filter query base on date field (4d)

Pull results from a stored procedure (4d)

Help With Query To Count Number of Pets & Vaccinations (5d)

Connect sql off network (5d)

- Advertisement -