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.
CREATE TABLE #PlayerDetails ( playerID INT, playerName VARCHAR(50), country VARCHAR(25) ); INSERT INTO #PlayerDetails SELECT playerID, FirstName, countryID FROM tblPlayer WHERE countryID = 1 --India
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.