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

Operand data type bit is invalid for max operator (19m)

SSIS Script IN (101m)

REPORTING SERVICES ERROR: "Index was outside the bounds of the array" (3h)

Log shipping in SQL Server 2008 R2 (7h)

Compatibility level changed from SQL server 2005 90 to SQL server 2012 110. The select into query missing the SORT operation in insert execution plan (12h)

Help with CASE in a WHERE Clause (21h)

Return value based on value inside query (22h)

Combine results from different datasets on one Tablix in Report Builder 3.0 (2d)

- Advertisement -