| 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. |