最详细的临时表,表变量的对比

Feature

Table Variable

Temp Table

Note

Table Name

Max 128 characters

Max 116 characters

 

Data Storage

In memory and TempDB

TempDB

 

Meta Data

In memory

TempDB

A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB.

Scope

Current batch

Current session

Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP.

Constraints

Allowed

Allowed

For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements.

DDL

Not allowed

Allowed.

E.g. create Index on the temp table.

Concurrent

Supported

Supported

Constraints and Indexes with explicit name in a temp table cause duplicate name error.

Statistics

Not supported

Supported

Estimated row number in execution plan for table variable is always 1

Parallel execution plan

Supported only for select

Supported

Parallel query execution plans are not generated for queries that modify table variables.

Transaction and Locking

Not participated

Participated

Data in table variable is not affected if the transaction is rolled back

Cause Recompile

No

Yes

Temp Table creation causes SPs/batches to recompile

SELECT INTO <t>

Not supported

Supported

 

INSERT <t> EXEC

Not supported

Supported

 

Use

UDFs, Stored procedures, Triggers, Batches

Stored procedures, Triggers, Batches

 Temp tables can't be used in UDFs.

 

from:http://www.sqlservercentral.com/articles/Table+Variables/63878/

 

Very Good~

posted on 2008-09-06 07:09  Keep Walking  阅读(17283)  评论(4编辑  收藏  举报