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 

In previous related articles I explained 20 main differences between Stored procedures and User defined functions in Sql Server and Difference between Delete and Truncate in sql server and Multiple Sql server queries to get second,third,fourth or nth highest salary

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

Examples to Declare and useTable Variables in sql server

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

• Tweet
• Share
• Share
• Share

Related Post