Saturday, February 7, 2015

SQL TEMP TABLE


SQL TEMP TABLE


The concept of temporary table is introduced by SQL server. It helps developers in many ways:
Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database.
There are two types of temp tables based on the behavior and scope.
  1. Local Temp Variable
  2. Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.
  1. CREATE TABLE #local temp table (  
  2. User id int,  
  3. Username varchar (50),  
  4. User address varchar (150)  
  5. )  

Global Temp Variable

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.
  1. CREATE TABLE ##new global temp table (  
  2. User id int,  
  3. User name varchar (50),  
  4. User address varchar (150)  
  5. )  

No comments:

Post a Comment