What is temporary table?

       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. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table :

1. table name is stared with single hash ("#") sign.

   
   CREATE TABLE #Local
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into #Local values ( 1, 'Shailendra','Noida');
   GO
   Select * from #Local
   
   

   
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2. Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

   
   CREATE TABLE ##Global 
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into ##Global values ( 1, 'Shailendra','Noida');
   GO
   Select * from ##Global 
    
   
   

   
   Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.