Share via

  • Article

table (Transact-SQL)

Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.

Note

To declare variables of type table, use DECLARE @local_variable.

Topic link iconTransact-SQL Syntax Conventions

Syntax

table_type_definition ::= 
    TABLE ( { column_definition | table_constraint } [ ,...n ] )column_definition ::= 
    column_name scalar_data_type 
    [ COLLATE collation_definition ] 
    [ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ] 
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ] 

column_constraint ::= 
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK (logical_expression ) 
    } 

table_constraint ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
     | CHECK (logical_expression ) 
     }

Arguments

  • table_type_definition
    Is the same subset of information that is used to define a table in CREATE TABLE. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.

    For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local\_variable (Transact-SQL).

  • collation_definition
    Is the collation of the column that is made up of a Microsoft Windows locale and a comparison style, a Windows locale and the binary notation, or a Microsoft SQL Server collation. If collation_definition is not specified, the column inherits the collation of the current database. Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

Remarks

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

Important

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

table variables provide the following benefits:

  • A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

    Within its scope, a table variable can 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 cannot be used in the following statement:

    SELECT select_list INTO table_variable

    table variables are automatically cleaned up 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 cause 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. Therefore, table variables require less locking and logging resources.

Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).

table variables can be referenced by name in the FROM clause of a batch, as shown the following example:

SELECT Employee_ID, Department_ID FROM @MyTableVar

Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID)

Assignment operation between table variables is not supported. Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.