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. |
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 )
}
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.
This discussion entry is lacking enough information to provide a decision as to use this or temporary tables.
i.e. Where is the data actually stored, memory or in tempdb?
What limits how much data can be placed into the variable? Memory?
Does sql server treat this table as a temporary table using tempdb to manage it or is it all done in memory?
If there's a paper or this data type, perhaps a link to the paper will help.
[GAILE-MSFT]: Both temp tables and table variables are stored in tempdb. There are conditions (described here: http://msdn.microsoft.com/en-us/library/ms345368(SQL.100).aspx) when the temp data is cached in memory.
- 1/18/2010
- timschwallie
- 7/6/2011
- Gail Erickson
table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.
Gail Erickson [MSFT]
SQL Server Documentation Team
- 7/6/2011
- Gail Erickson
- 4/26/2011
- genuye
DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'London')
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt
And this code shows the update after the rollback
DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
BEGIN TRANSACTION IdentityTest
UPDATE @MyTable SET MyCity = 'Denver'
ROLLBACK TRANSACTION IdentityTest
INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
SELECT * FROM @MyTable mt
It would be nice if somewhere in BOL there was an explicit entry that discussed this and notes added to content like this to clarify this. Numerous entries on social.msdn.microsoft.com answer these questions
- 9/8/2010
- way0utwest
I was trying to do it for having the result of 'RESTORE FILELISTONLY' independent of version SQL server adding the column TDEthumprint only if version>=10, but it was not successful
- 2/3/2010
- Sérgio L
