Friday, September 20, 2013

What is difference between Table Variable and Temp Table

Feature
Table Variable
Temporary Table
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.
Indexes
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
Statements are not allowed.
Statements are allowed.
Truncate table
Not allowed.
Allowed.
Rollbacks
Not affected (Data not rolled back).
Affected (Data is rolled back).

No comments: