Based on the behavior and scope of the table SQL Server provides two types of temporary tables as listed below.
- Local Temp Table
- Global Temp Table
Local Temp Table
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#”) sign.
CREATE TABLE #LocalTemp ( UserID int, Name varchar(50), Address varchar(150) ) GO insert into #LocalTemp values ( 1, 'Brijesh Kumar','Noida'); GO Select * from #LocalTemp
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.
Global Temp Table
Global Temporary tables name starts with a double hash (“##”). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
CREATE TABLE ##GlobalTemp ( UserID int, Name varchar(50), Address varchar(150) ) GO insert into ##GlobalTemp values ( 1, 'Brijesh Kumar','Noida'); GO Select * from ##GlobalTemp
Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.