Knowledge is no fun, unless you share it!!! :)

Thursday, 26 September 2013

Temporary Table in SQL Server 2012 and It's Usage

Today i am going to tell about the Temporary tables. There are some myths about Temporary tables and there usages.
 
Many time we confuse while using this temporary tables.
What should be use Temporary table or Table variables?
But this topic i am going to cover in my next article.
 
Today we see how Temporary table make out life more easy!!
 
Usage of Temporary table is an effective mechanism available in all versions of SQL Server.
 
Temporary table:
It work like a regular table in that you can perform the operations select, insert and delete as for a regular table. If created inside a stored procedure they are destroyed upon completion of the stored procedure.
It stores in TempDB database.
 
There are two type of Temporary table availables -
  • Local Temporary Tables.
  • Global Temporary Tables.
Local Temporary Tables-
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.
It stores in TempDB database.
 
Global Temporary Table-
Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.We can not create global temporary tables with same name in multiple window since it exists until we close all the sessions.
It also stores in TempDB database.
 
Physical location of Temporary table -
 
Whenever we create a temporary table, it goes to the Temporary folder of the tempdb database. tempdb -> temporary tables.
 
How to Drop Temporary Tables-
We can delete the temporary tables using the drop command as follows:
 
DROP TABLE #Temporary_Table
 

 Temporary tables are slighly diffrent from normal tables in following ways -
 
  • Temporary tables are always stores in TempDB database inside "Master Database". Tables are stores in there respective databases.
  • Temporary tables are implicitly dropped by system after killing all sessions. Tables have to drop explicitly by users.
Other functionality available in Temporary table-
 
It allows DDL operations i.e. Truncate, Alter Table.
It allows both clustered indexes and nonclustered indexes creations over the columns.
It can access in nested stored procedures.
It can participate in a transaction.
It writes to Log File.
It allows creation of statistics.
It can perform "Select Into" operation.
It can define globally.

 
Temporary tables have some limitations also that is we can not use it inside user define functions.
 
We will see about table variables and diffrence between Temporary table and table variable on my next article.
 
Feel free to shoot your ideas on this!!!
Thanks :)