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!