Transact-SQL Reference

table

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.

Syntax

Note  Use DECLARE @local_variable to declare variables of type table.

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 ( search_condition )
    }

Arguments

table_type_definition

Is the same subset of information 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, CREATE FUNCTION, and DECLARE @local_variable.

collation_definition

Is the collation of the column consisting of a Microsoft® Windows™ locale and a comparison style, a Windows locale and the binary notation, or a Microsoft SQL Server™ collation.

Remarks

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:

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.

See Also

COLLATE

CREATE FUNCTION

CREATE TABLE

DECLARE @local_variable