Temporary Tables in SQL

What are Temporary Table

Temporary tables are very similar to permanent tables. A permanent table is created in the database you specify and remains in the database permanently, until you delete it. On the other hand temporary tables are created in the TempDB database (you can find tempdb in the system database) and are automatically deleted when they are no longer in use.

When to Use

1. When we are doing a large number of row manipulations in Stored Procedures.
   
   
2. This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
   
   
3. When we are having a complex join operation.

Various types of Temporary tables

These are basically two types:

1. Local temporary table: A temporary table name always start with the suffix # (Hash). A local temporary is available only for the connection that has created the table, in other words when you open a new query window you cannot access the temporary table.
   
   Note: It is automatically dropped when the connection that created table is closed. If the temporary table is created inside the Stored Procedure it will automatically be dropped when the execution of the procedure is completed.
   
   Remember: It is possible for a different connection to create a local table with the same name. To handle name duplication of the table SQL Server appends a random number to the table name.
   
   Create Syntax:
   

   Create table #TableName (ID INT, NAME VARCHAR(30) )
   Drop syntax: drop table #TableName
   Select Syntax: Select * from #TableName

2. Global Temporary table: To create the global temporary table we need to suffix the table name with two pound symbols (##). It is visible to all connections of the SQL Server and are only dropped when the last connection referencing the table is closed.
   
   Note: The global table must have a unique name.
   
   Create Syntax:
   

   Create table ##TableName (ID INT, NAME VARCHAR(30) )
   Drop syntax: drop table # #TableName
   Select Syntax: Select * from # #TableName

Features of Temp Tables

1. We can index temporary tables. And we can declare the primary key with the table as well as have relationships.
   
   
2. The inserted temp table record can be rolled back.

Difference between local & global temporary table

1. The local table prefix is a single pound (#) symbol, whereas the global table is prefixed with two pound symbols (##).
   
   
2. SQL Server appends a random number to the local temporary table but this is not the case with global temporary tables.