Temporary Table:


Temporary tables are very similar to the permanent tables. Permanent tables are created in the database where you specify, and it remains in the database permanently until you delete (drop) them. And in the case of the temporary table get created in TempDB and are automatically deleted, when they are no longer used. 

 Different Types of Temporary tables:
1.      Local Temporary Tables.
2.      Global Temporary tables.    
     
 1.  Local Temporary Tables 


  A local temporary table is available, only for the connection that has created the


table.

 A Local temporary table is automatically dropped. When the connection that has

created     

 Then it, is closed.

 To create a Local Temporary table, prefix the name of the table with 1 pound (#)

symbols.

 CREATE TABLE #EmployeeDetail(Id int, Name nvarchar(20))


  Inserting Data into the temporary table:
    Insert into # EmployeeDetails Values(1, ‘Tidda’)
     Insert into # EmployeeDetails Values(2, 'Bolero')
    Insert into # EmployeeDetails Values(3, 'Vilaa')


  Select the data from the temporary table:
   Select * from # EmployeeDetails

 If user are want to delete or Drop the Temp table then use:

  DROP TABLE #EmployeeDetails

 It is also possible for different connections, to create a local temporary table with

the    

 Same name.

 For example: UserA and UserB both can create a local temporary table with the

same

  Name #UserDetails. 

 Check if the Local temporary table is created:

You can check the existence of temporary tables using object explorer like:



Note:   If you want to create a Temporary table inside the body of Stored procedure then

   it is automatically destroyed after execution of stored procedure. 

 2. Global Temporary tables

  When you want to create Global temporary table then you use 2 pounds (##) symbols

    front of the table name.

  Create Table ##EmployeeDetails(Id int, Name nvarchar(20))

  Global temporary tables are visible to all the connection of the SQL Server, and are only

 Destroyed when the last connection or last Query window is closed.

  Multiple users on multiple connections can have local temporary table with the same

Name, but, a Global temporary table name has to be unique, and if you inspect the name

Of the global temp table, in the object explorer not add any random numbers at end of the

  Table name. 

 Difference between Local and Global Temporary Tables:

1.  Local temp tables are created with the help of single pound (#) symbol, whereas Global temp tables are prefixed with 2 pound (##) symbols.

2.      SQL Server add some random numbers end of the local temp table name, where Global temp table not add any random numbers end of the name.

3.      Local temp table are only visible to that query window of the SQL Server which has created it, whereas global temporary tables are visible to all the SQL server sessions.            


4.     A Local temporary table is automatically dropped. When the connection that has created then it, is closed, where Global temporary tables are only destroyed when the last connection or last Query window is closed.


  Modified On Nov-29-2017 09:04:56 AM

Leave Comment