Difference between Table Variable and temporary table
|Feature||Table Variables||Temporary Tables|
|Scope||Current batch||Current session, nested stored procedures. Global: all sessions.|
|Usage||UDFs, Stored Procedures, Triggers, Batches.||Stored Procedures, Triggers, Batches.|
|Creation||DECLARE statement only.||
CREATE TABLE statement.
SELECT INTO statement.
|Table name||Maximum 128 characters.||Maximum 116 characters.|
|Column data types||
Can use user-defined data types.
Can use XML collections.
|User-defined data types and XML collections must be in tempdb to use.|
|Collation||String columns inherit collation from current database.||String columns inherit collation from tempdb database.|
|Indexes||Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.||Indexes can be added after the table has been created.|
|Constraints||PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.||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.|
|Post-creation DDL (indexes, columns)||Statements are not allowed.||Statements are allowed.|
|Data insertion||INSERT statement (SQL 2000: cannot use INSERT/EXEC).||
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
|Insert explicit values into identity columns (SET IDENTITY_INSERT).||The SET IDENTITY_INSERT statement is not supported.||The SET IDENTITY_INSERT statement is supported.|
|Truncate table||Not allowed.||Allowed.|
|Destruction||Automatically at the end of the batch.||Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)|
|Transactions||Last only for length of update against the table variable. Uses less than temporary tables.||Last for the length of the transaction. Uses more than table variables.|
|Stored procedure recompilations||Not applicable.||Creating temp table and data inserts cause procedure recompilations.|
|Rollbacks||Not affected (Data not rolled back).||Affected (Data is rolled back).|
|Statistics||Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.||Optimizer can create statistics on columns. Uses actual row count for generation execution plan.|
|Pass to stored procedures||SQL 2008 only, with predefined user-defined table type.||Not allowed to pass, but they are still in scope to nested procedures.|
|Explicitly named objects (indexes, constraints).||Not allowed.||Allowed, but be aware of multi-user issues.|
|Dynamic SQL||Must declare table variable inside the dynamic SQL.||Can use temporary tables created prior to calling the dynamic sql.|