Related Posts Plugin for WordPress, Blogger...

About

Follow Us

Friday, 13 February 2015

Introduction:
 In this article I am going to explain Temporary tables vs. Table variables in SQL Server 
We use Table variable and Temp table to hold data for further processing temporarily. But there is lot of difference between these two i.e. Scope, type etc.
I have tried to list all the difference between Table variable and temporary table (as per knowledge)

Temporary Table
Table Variable
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table.

Temporary tables can be used in Stored Procedures, Triggers and Batches but not in user defined functions
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch.

Table variables can be used in user defined functions, stored procedures, and batches
Temp Tables are physically created in the Tempdb database.
This is also created in the Tempdb database but not the memory
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.
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
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 tablevariables 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

 

 

 


 

0 comments:

Post a Comment