Difference between Temporary Table and Table Variable in Sql Server


Introduction: In this article I am going to explain Temporary tables vs Table variables in Sql Server 


Both the Temporary Tables and Table Variables in Sql Server provide a mechanism for storing of the result-set temporarily for further processing. To get detailed knowledge of these two you must read the articles Temporary tables in sql server, their types and examples to use and

   Below are the list of differences between these two:
Temporary Table
Table Variable
Temporary tables can be used in Stored Procedures, Triggers and Batches but not in user defined functions

Table variables can be used in user defined functions, stored procedures, and batches
Local temporary tables are temporary tables that are available only to the session that created them. Global temporary tables are temporary tables that are available to all sessions and all the users.
Its scope is in the stored procedure, user defined function or batch where it is declared like any local variable we create with a DECLARE statement.
Local temporary tables are automatically destroyed at the end of the procedure or session that created them. Global temporary tables are dropped automatically when the last session using the temporary table has completed
We can also drop temporary tables explicitly using drop command similar to normal table.

Table variables are automatically cleaned up at the end of the user defined function, stored procedure, or batch in which they are defined.
Temporary table name can be of maximum 116 characters
Table variable name can be of maximum 128 characters
PRIMARY KEY, UNIQUE, NULL, CHECK etc can be implemented at the time of creating temporary tables using CREATE TABLE statement or can be added after the table has been created. FOREIGN KEY not allowed.

PRIMARY KEY, UNIQUE, DEFAULT values, NULL, CHECK can be added, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
Temporary table supports adding Indexes explicitly even after creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
Table Variables doesn’t allow the explicit addition of Indexes after it is declared, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined at the time of declaring Table Variable.
Temporary tables can also be directly created and data can be inserted using Select Into statement without creating a temporary table explicitly.
Table variables can’t be created using Select Into statement because being a variable it must be declared before use

The SET IDENTITY_INSERT statement is supported in temporary table
The SET IDENTITY_INSERT statement is not supported in table variables
We can’t return a temporary table from a user-defined function

We can return a table variable from a user-defined function

Temporary Table can be truncated like normal table

Table variables can’t be truncated like normal table or temporary tables.

The data in the temporary table will be rolled back when a transaction is rolled back similar to normal table

The data in the table variable will not be rolled back when a transaction is rolled back

Temporary tables used in stored procedures cause more recompilations of the stored procedures than when table variables are used.

Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.

A temporary table will generally use more resources than table variable

A table variable will generally use less resources than a temporary table

Temporary tables can be access in nested stored procedures

Tables variables can’t be access in nested stored procedures

Can be altered using ALTER command
Does not support ALTER command

Temporary tables should be used for large result sets.
Table variables should be used for small result sets and the everyday type of data manipulation since they are faster and more flexible than temporary tables

Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

3 comments

Click here for comments
September 02, 2015 ×

I am glad you found this article helpful..stay connected and keep reading for more useful updates

Reply
avatar
September 02, 2015 ×

thanks for your valuable feedback.

Reply
avatar
Unknown
admin
July 13, 2022 ×

Excellent Article

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..