Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

1. Local Temp Table
2. 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.

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.

Creating Local Temporary Table:

CREATE TABLE #temp_tblEmployee
(
        [Emp_ID] [int] NOT NULL,
        [Emp_Name] [varchar](max) NOT NULL
)
INSERT INTO #temp_tblEmployee
SELECT 1,'Shashangka Shekhar'
GO

SELECT * FROM #temp_tblEmployee

Creating Global Temporary Table:

CREATE TABLE ##temp_tblEmployee
(
        [Emp_ID] [int] NOT NULL,
        [Emp_Name] [varchar](max) NOT NULL
)
INSERT INTO ##temp_tblEmployee
SELECT 1,'Shashangka Shekhar'
GO

SELECT * FROM ##temp_tblEmployee

When to Use Temporary Tables?

1. When we are doing large number of row manipulation 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.