Difference between Table Variables and Temporary Tables
Difference between Table Variables and Temporary Tables
In this article i will explain the difference between table variables and temporary tables in sql server 2005/2008/2012
Feature
|
Table Variables
|
Temporary Tables
|
Scope
|
Current batch
|
Current session,
nested stored procedures. Global: all sessions.
|
Creation
|
DECLARE statement
only.
|
CREATE TABLE
statement.
SELECT INTO statement.
|
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.
|
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.)
|
Rollbacks
|
Not affected (Data not
rolled back).
|
Affected (Data is
rolled back).
|
Dynamic SQL
|
Must declare table
variable inside the dynamic SQL.
|
Can use temporary
tables created prior to calling the dynamic sql.
|
Usage
|
UDFs,
Stored Procedures, Triggers, Batches.
|
Stored
Procedures, Triggers, Batches.
|
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.
|
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.
|
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.
|
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.
|
0 comments:
Post a Comment