TABLE variables in T-SQL

[Andrew Hauger]

I have used table variables in other database environments, but not SQL
Server. How are they implemented in SQL Server? Are they simply TEMP tables
stored on disk and referenced with variable names, or are they created in
memory? What are the limits on TABLE variables? Books Online helped me find
a starting point for this topic, but did not give enough information
regarding it.

Thanks in advance for any help you can provide!

Andy

[MrQwer]

Andrew,

From BOL

Functions and variables can be declared to be of type table. table variables
can be used in functions, stored procedures, and batches.

Use table variables instead of temporary tables, whenever possible. table
variables provide the following benefits:

a.. A table variable behaves like a local variable. It has a well-defined
scope, which is the function, stored procedure, or batch in which it is
declared.
Within its scope, a table variable may be used like a regular table. It
may be applied anywhere a table or table expression is used in SELECT,
INSERT, UPDATE, and DELETE statements. However, table may not be used in the
following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function,
stored procedure, or batch in which they are defined.

b.. table variables used in stored procedures result in fewer
recompilations of the stored procedures than when temporary tables are used.

c.. Transactions involving table variables last only for the duration of
an update on the table variable. Thus, table variables require less locking
and logging resources.
Assignment operation between table variables is not supported. In addition,
because table variables have limited scope and are not part of the
persistent database, they are not impacted by transaction rollbacks.

Is this any help or could you specify your question a little?

/M

"Andrew Hauger" <a_ha...@yahoo.com> wrote in message
news:9sll8.27479$Dv6.8...@typhoon.austin.rr.com...