Click here to Skip to main content
Click here to Skip to main content
Go to top

Table Variable V/S Temporary Table

, 7 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Difference between Table Variable and temporary table Feature Table Variables Temporary Tables Scope Current batch Current session, nested stored procedures. Global: all sessions. Usage

Difference between Table Variable and temporary table

Feature Table Variables Temporary Tables
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.

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.
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.
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.

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.)
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.
Rollbacks Not affected (Data not rolled back). Affected (Data is rolled back).
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.
Dynamic SQL Must declare table variable inside the dynamic SQL. Can use temporary tables created prior to calling the dynamic sql.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tejas Vaishnav
Software Developer
India India
No Biography provided

Comments and Discussions

 
GeneralThanks.. we Can use it as Refernce..... PinprofessionalRaja Sekhar S9-Jun-13 18:37 
GeneralRe: Thanks.. we Can use it as Refernce..... PinmemberMember 108079769-May-14 18:43 
GeneralMy vote of 5 Pinmembersakti_shankar18-Mar-13 20:32 
GeneralMy vote of 1 PinmemberElwood_BDR22-Jan-13 22:05 
GeneralMy vote of 1 Pinmembereinstein's brain9-Nov-12 4:09 
AnswerSuggestion PinmemberStrange_Pirate28-Sep-12 1:21 
GeneralHa ha Your are kicked off [modified] Pinmemberkicker20129-Jul-12 0:38 
People read the beter and orginal blog here
 
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/[^]
 
its better to Throw Those Trolls Away. request you all DOWN VOTE HIM

modified 10-Jul-12 9:18am.

QuestionShort and simple Pinmembermeeram3954-Jul-12 2:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 7 Jul 2012
Article Copyright 2012 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid