Results 1 to 2 of 2

Thread: Table variable

  1. #1
    Join Date
    Oct 2003

    Unanswered: Table variable

    May I have an idea about table variable?I want to know the scope and capacity of a table variable?How much data can it hold maximum?

  2. #2
    Join Date
    Jan 2003
    From BOL:
    A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

    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 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.
    * CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

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

    * 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts