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).
|
Friday, September 20, 2013
What is difference between Table Variable and Temp Table
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment