Wednesday, January 30, 2013

Difference Temporary Table and Table Variable –SQL SERVER?

Posted by Rahul Kharde at 1:32 AM

Temporary Table
Table Variable
create table #T (…)
declare @T table (…)
Temporary Tables are real tables so you can do things like CREATE INDEX,
Table variable is not real table but you can have indexes by using PRIMARY KEY or UNIQUE constraints.
CREATE TABLE statement.
SELECT INTO statement.
DECLARE statement  Only
Maximum 116 characters.
Maximum 128 characters
Temp tables might result in stored procedures being recompiled,
Table variables will not.
#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
User-defined data types and XML collections must be in tempdb to use
Can use user-defined data types and XML collections.
Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Automatically at the end of the batch.
Last for the length of the transaction. Uses more than table variables.
Last only for length of update against the table variable. Uses less than temporary tables.
Creating temp table and data inserts cause procedure recompilations.
Stored procedure recompilations Not applicable.
Data is rolled back
Data not rolled back
Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.
The SET IDENTITY_INSERT statement is supported.
The SET IDENTITY_INSERT statement is not supported.
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
INSERT statement (SQL 2000: cannot use INSERT/EXEC).
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.
PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
Indexes can be added after the table has been created.
Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.

Example

CREATE TABLE #Temp
(
          Col1 INT IDENTITY,
          Col2 VARCHAR(100)
)

DECLARE @Temp TABLE
(
          Col1 INT IDENTITY,
          Col2 VARCHAR(100)
)

INSERT INTO #Temp(Col2) select 'Temp Table'
INSERT INTO @Temp(Col2) select 'Table Variable'

SELECT * FROM #Temp
SELECT * FROM @Temp

DROP TABLE #Temp




If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top