Saturday, January 18, 2014

TEMPORARY TABLE VS TABLE VARIABLE


TABLE Variable
Temporary TABLE
Current batch
Current session nested stored procedures. Global: all sessions.
UDFs, Stored Procedures, Triggers, Batches.
Stored Procedures, Triggers, Batches.
DECLARE statement only.
CREATE TABLE statement.
SELECT INTO statement.
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.
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.
The SET IDENTITY_INSERT statement is not supported.
The SET IDENTITY_INSERT statement is supported.
 Truncation Not allowed.
 Truncate Allowed.
Not affected (Data not rolled back).
Affected (Data is rolled back).

No comments:

Post a Comment