Temporary Tables in MS SQL Server

A temporary table in SQL Server is a table that exists temporarily on the database server for a certain period of time. Temporary tables are basically stored inside the “tempdb”, which is a system database. 

Temporary tables are useful to work with a small subset of records in a table that contains a large number of records. Instead of processing a small set of data again and again from a large table, filter and store it once in a temporary table.

Types of temporary tables

The two types of temporary tables are local and global temporary tables.

Local Temporary Table

The local temporary table has a pound sign (#) as the first character of the name. Creating a temp table is similar to creating a permanent table with a pound sign as a prefix in the table name. You can create the table using a CREATE TABLE statement or SELECT INTO statement. Let’s see an example to get the list of players belongs to India and store it in a temp table.

Method 1

CREATE TABLE #PlayerDetails 
(
    playerID INT, 
    playerName VARCHAR(50), 
    country VARCHAR(25)
);

INSERT INTO #PlayerDetails 
SELECT playerID, FirstName, countryID  
FROM tblPlayer  
WHERE countryID = 1 --India

Method 2

SELECT 
    playerID, 
    FirstName 
INTO #PlayerDetails  
FROM tblPlayer  
WHERE countryID = 1 --India

Any one of the above queries creates the local temporary table ‘#PlayerDetails’ in ‘tempdb’. To verify the created temp table in the server, expand ‘tempdb’ under system database in the object explorer window. See the below image.  

In the above screen, notice that the temp table name has some values at the end. It is the unique identifier and SQL server generates it automatically. This is because the multiple database connections can create the same temp table. To differentiate the temp tables created by different connections, the unique identifier is appended at the end of the table name. 

The scope of the local temp table is available only for the connection that has created the table. You should perform the DDL/ DML operations on the temp table in the same connection. When you close the connection that creates the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.

Global Temporary Table 

The global temporary table has two-pound signs (##) as the first character of the name.

SELECT 
    playerID, 
    FirstName 
INTO ##PlayerDetails  
FROM tblPlayer  
WHERE countryID = 1 --India 

In contrast to local temporary table, the scope of the global temp table is available to all the open connections. Since it can be accessed by all connections, no unique identifier is added at the end of the table name.

When you close the last connection that references the temp table, it drops automatically. You can also drop the temp table manually by using DROP TABLE statement.

Like permanent tables, temp tables can have constraints and indexes. 

Difference between local and global temp table

Local Temp Table	Global Temp Table
The table name has the single pound ‘#’ sign as the prefix.	The table name has the double pound ‘##’ sign as the prefix.
A unique identifier is appended at the end of the table name.	No unique identifier is appended to the table name.
The scope of the table is available only for the connection.	The scope of the table is available for all the open connections.
The table is dropped automatically when the connection that created the table is closed.	The table is dropped when the last connection that references it is closed.

The limitation of the temp table is that the user-defined function cannot contain the temp tables.