Presents your 
SQL SERVER E-NEWSLETTER for April 1, 2003

<------------------------------------------->

USE THE TABLE DATA TYPE INSTEAD OF TEMPORARY TABLES

Table is a special data type new to SQL Server 2000 that can store a
result set for later use. Normally developers use it for temporary storage
of a set of rows that are to be returned as a table-valued function.

Like any other local variable, you use the DECLARE @local_variable
statement to declare table variables. The table data type definition includes
column definitions, names, data types, and constraints; however,
constraints are limited to PRIMARY KEY, UNIQUE KEY, and NULL.

A table variable behaves like a local variable in that it exists for the
defined scope of the function, stored procedure, or batch in which it's
declared. The variable is automatically cleaned up at the end of the
function. If you use a temporary table to store your result set, you 
have the residual table to deal with after your stored procedure or 
function executes.

Table variables also result in fewer recompilations of the stored
procedures in which they're used, and since they're not part of 
persistent data, transaction rollbacks don't impact them.

Because table variables only last the duration of an UPDATE statement,
they also require less locking and logging resources.

You can apply a table variable anywhere table is used in SELECT, 
INSERT, UDPATE, and DELETE statements.

However, they cannot be used in the following statement:

INSERT INTO table_variable EXEC stored_proecedure
SELECT select_list INTO table_variable

----------------------------------------