|
Temporary Table
|
Table Variable
|
| create table #T (…) |
declare @T table (…)
|
| Temporary Tables are real tables so you can do things like CREATE INDEX, |
Table variable is not real table but you can have indexes by using PRIMARY KEY or UNIQUE constraints.
|
| CREATE TABLE statement. SELECT INTO statement. |
DECLARE statement Only
|
|
Maximum 116 characters.
|
Maximum 128 characters
|
|
Temp tables might result in stored procedures being recompiled,
|
Table variables will not.
|
#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends. |
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end. |
| User-defined data types and XML collections must be in tempdb to use | Can use user-defined data types and XML collections. |
| Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.) | Automatically at the end of the batch. |
| Last for the length of the transaction. Uses more than table variables. | Last only for length of update against the table variable. Uses less than temporary tables. |
| Creating temp table and data inserts cause procedure recompilations. | Stored procedure recompilations Not applicable. |
| Data is rolled back | Data not rolled back |
| Optimizer can create statistics on columns. Uses actual row count for generation execution plan. | Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans. |
| The SET IDENTITY_INSERT statement is supported. | The SET IDENTITY_INSERT statement is not supported. |
| INSERT statement, including INSERT/EXEC. SELECT INTO statement. |
INSERT statement (SQL 2000: cannot use INSERT/EXEC). |
| PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed. | PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed. |
| Indexes can be added after the table has been created. | Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. |
Example
CREATE TABLE #Temp
(
Col1 INT IDENTITY,
Col2 VARCHAR(100)
)
DECLARE @Temp TABLE
(
Col1 INT IDENTITY,
Col2 VARCHAR(100)
)
INSERT INTO #Temp(Col2) select ‘Temp Table’
INSERT INTO @Temp(Col2) select ‘Table Variable’
SELECT * FROM #Temp
SELECT * FROM @Temp
DROP TABLE #Temp
