 |
|
Table variables |
|
| 07 Feb 2007 | McGraw-Hill |
|
|
Table Variables
Table variables are objects similar to temporary tables and were introduced in SQL Server 2000. A table variable is declared using the table data type. A statement declaring a table variable initializes the variable as an empty table with a specified structure. As a table definition, such a statement includes definitions of columns with their data type, size, precision, and optional constraints (primary key, identity, unique, and check constraints). All elements have to be defined during the declaration. It is not possible to alter or add them later.
The following batch declares a table variable, inserts rows, and returns them to the user:
Declare @MyTableVar table
(Id int primary key,
Lookup varchar(15))
Insert @MyTableVar values (1, '1Q2000')
Insert @MyTableVar values (2, '2Q2000')
Insert @MyTableVar values (3, '3Q2000')
Select * from @MyTableVar
Go
Because of their nature, table variables have certain limitations:
Table variables can only be part of the Select, Update, Delete, Insert, and Declare Cursor statements.
Table variables can be used as a part of the Select statement everywhere tables are acceptable, except as the destination in a Select...Into statement:
Select LookupId, Lookup
Into @TableVariable -- wrong
From Lookup
Table variables can be used in Insert statements except when the Insert statement collects values from a stored procedure:
Insert into @TableVariable -- wrong
Exec prMyProcedure
Unlike temporary tables, table variables always have a local scope. They can be used only in the batch, stored procedure, or function in which they are declared.
Table variables are considered to be nonpersistent objects, and therefore they will not be rolled back after a Rollback Transaction statement.
TIP
If possible, use table variables instead of temporary tables. Table variables have less locking overhead and therefore are faster.
Basic Transact-SQL Programming Constructs
 Home: Introduction
Tip 1: T-SQL identifiers
Tip 2: Database object qualifiers
Tip 3: Character string data types
Tip 4: Date, time and number data types
Tip 5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip9: Table variables
Tip 10: Flow control statements
Tip 11: Blocks, looping and conditional statements
Tip 12: Unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
