Temporary Tables – MS SQL Server

Yesterday, we had covered the basics of temporary tables in Oracle. Today, we will cover the basic features of temp tables in MS SQL Server. Unlike Oracle, MS SQL Server has local and global temporary tables. Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Local Temporary tables:

They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign. When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Open one session in Query Analyzer or SSMS (Management Studio) and create a temporary table as shown below.

CREATE TABLE #TEMP
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO

Upon successful execution of above command, MS SQL Server creates table in tempdb database. One cannot create another temporary table with the same name in the same session. It will give an error but table with the same name can be created from another session. To do this, open another session from SSMS or query analyzer and issue same command again. It will successfully create new temporary table for that session.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number. This is very common scenario when temporary table is defined in the stored procedure and procedure is getting executed by different users simultaneously. Since we have created temporary table with the same name from two different sessions, we should see two entries in tempdb database. From another session or any of the current session, issue following command. Output is displayed after select statement.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb #TEMP________0000000001F7
tempdb #TEMP________0000000001F9

Now create some data from the session in which temporary table (#temp) is created.

INSERT INTO #TEMP(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO #TEMP(COL1, COL2) VALUES(2,’Information’);
INSERT INTO #TEMP(COL1, COL2) VALUES(3,’systems’);

Selecting data from temporary table will give following results.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-27 19:39:56.727
2 Information 2007-03-27 19:39:56.727
3 systems 2007-03-27 19:39:56.727

This data is not visible from another session since we are using local temporary table. We can verify it by connecting to another session and querying the #temp table. Local temporary tables are dropped when session which created the table is ended, if one has not dropped it explicitly.

Also, please do note that if you are creating temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over (they can be explicitly dropped as well). Once the procedure execution is over, those temp tables will not be accessible from within that session. Example:

create proc test
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
end
go

exec test
select * from #temp

Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#temp’.

Global Temporary tables:

Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition. In contrast of local temporary tables, global temporary tables are visible across entire instance.

CREATE TABLE ##TEMP_GLOBAL
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO
Execute above statement to create global temporary table. You can verify it by checking the tempdb database. As global temporary tables are available across the instance, SQL Server doesn’t suffix it with the number. Following is the output of query ran against tempdb.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘##TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb ##TEMP_GLOBAL

There will be only single instance of global temporary table. Attempt of creating global temporary table with the same name from any other session will result into an error.

Create some data in one of the session where temporary table (##temp_global) is created.

INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(2,’Information’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(3,’systems’);

Connect to other existing session or open new session. Execute following statement and you will notice that global temporary table is available along with the data from other session as well.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-28 09:52:34.310
2 Information 2007-03-28 09:52:34.310
3 systems 2007-03-28 09:52:34.310

Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it. This will help us in avoiding creation error when same connection from the connection pool is used by different processes which access temporary tables.

Global temporary tables can be used in data warehousing application where one session performs the ETL and populate the global temporary tables and other sessions read from the table, specific data and process it.

A possible replacement for temp tables is a table variable. We had blogged that before – you can read more on that here.

In summary, following are the key points when temporary tables are involved.

• Temporary tables can be defined as local or global temporary tables.
• Local temporary tables are available to session in which they are created. If another session creates the table with the same name, it will be different copy of the table in tempdb database.
• Global temporary tables are available across the instance. Any user from any session can access it.
• It is best practice to drop the temporary table when related work is finished rather than relying on connection to end for the cleanup.
• Table variables can be used instead of temporary tables for performance reasons and when dealing with smaller sub-sets.
• When used in the procedure,function or trigger, its scope ends once execution is completed.