Local and Global Temporary Tables in SQL Server

• Local Temporary Tables
• Global Temporary Tables
• Temporary Table Limitations
• Emulating Oracle Package Variables using Temporary Tables

In SQL Server, you can use local and global temporary tables.

Local temporary tables are visible only in the current session, while global temporary tables are visible to all sessions.

Unlike Oracle, SQL Server does not store the definition of temporary tables permanently in the database catalog views, and this can cause various scope and visibility issues when you use temporary tables.

A local temporary table is created using CREATE TABLE statement with the table name prefixed with single number sign (#table_name).

In SQL Server, local temporary tables are visible only in the current session. So if you create a local temporary table in one session, you cannot access it in other sessions.

Local Temporary Table Scope

If a local temporary table created in a stored procedure, it is dropped automatically when the stored procedure is finished.

This means that this local temporary table can be referenced only by nested stored procedures.

The local temporary table cannot be referenced by the stored procedure or application that called the stored procedure that created the local temporary table.

Example

   CREATE PROCEDURE sp_create_tempt
   AS
    CREATE TABLE #temp1
      (c1 INT);
 
   CREATE PROCEDURE sp_use_tempt
   AS
   BEGIN
    EXEC sp_create_tempt
    SELECT * FROM ##temp1
   END

    EXEC sp_use_tempt

No errors shown during procedures creation, but we get a run-time error.

A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).

In SQL Server, global temporary tables are visible to all sessions (connections). So if you create a global temporary table in one session, you can start using it in other sessions.

Session 1

  CREATE TABLE ##temp1 
    (c1 INT);
  INSERT INTO ##temp1 
    VALUES (1);

Session 2

  SELECT * FROM ##temp1

Global Temporary Table Scope

Global temporary table is automatically dropped when the session that created the table ends and the last active Transact-SQL statement (not session) referencing this table in other sessions ends.

Session 1 - Terminated

Session 2 (another connection)

  SELECT * FROM ##temp1

You cannot access local and global temporary tables in functions (UDFs):

   CREATE FUNCTION func_temptables1()
   RETURNS VARCHAR(50)
   AS
   BEGIN
     DECLARE @value VARCHAR(50)
     SELECT @value = value FROM ##temp_table
     RETURN @value
   END

In Oracle, package variables are global variables that can store values until the end of the session, and shared between all procedures and functions in the package.

Package variables are not shared between Oracle sessions (connections), each session has its own copy of data in the variables.

    CREATE OR REPLACE PACKAGE pack1
    IS
      name VARCHAR2(30) := 'Company Name';
      cdate DATE := SYSDATE;
   END;

Local Temporary Tables

You can use local temporary tables to emulate Oracle package variables. But due to visibility limitations (see above), you have to create and initialize a local temporary table in the application (for example, right after connection).

    CREATE TABLE #pack1_vars
    ( 
       name VARCHAR(30), 
       cdate DATETIME
    );   
 
    INSERT INTO #pack1_vars 
       VALUES ('Company Name', GETDATE());

Then you can use SQL SELECT and UPDATE statements to retrieve and update values:

  DECLARE @name VARCHAR(30)
  SELECT @name=name FROM #pack1_vars
 
  UPDATE #pack1_vars 
     SET name='New value'

Advantages: Relatively easy to use, no conflicts with other sessions, data are cleaned automatically
Disadvantages: Needs creation by each application, not easy to track and manage in case of large number of local temporary tables (a lot of packages with variables).

Global Temporary Tables

You can use global temporary tables to emulate Oracle package variables. In contract to local temporary tables, once you create a global temporary table, it becomes visible in any procedures and application.

But the global temporary table is also visible in other sessions, so you need to add some logic to avoid conflicts.

• Single global temporary table for one package used by all sessions

To emulate variables of a single package, you cannot create a single global temporary table in one session and use it in all other sessions.

The reason is that when the session that created the global temporary table terminates, the table is automatically dropped, and other sessions will lose their data.

• Unique global temporary table for each session

Every session should create its own global temporary table for each package containing variables. You can use @@spid function to generate unique table names in each session.

Before you can use variables, you have to call initialization code that creates and initializes the table in procedures or functions that use the variable.

   CREATE PROCEDURE pack1_init
   AS
   BEGIN
    IF NOT EXISTS(SELECT * FROM tempdb..sysobjects 
       WHERE id = object_id('tempdb..##pack1_vars_' + CAST(@@spid AS VARCHAR)) and xtype='U' )
    BEGIN
       EXEC ('CREATE TABLE ##pack1_vars_' + @@spid + ' (name VARCHAR(30), cdate DATETIME)')   
       EXEC ('INSERT INTO ##pack1_vars_' + @@spid + ' VALUES (''Company Name'', GETDATE())')
    END
 END

Then you can use user-defined functions to get and set variable values:

   CREATE PROCEDURE pack1_name_set (@name VARCHAR(30))
   AS
     EXEC ('UPDATE ##pack1_vars_' + @@spid + ' SET name = ''' + @name + '''')

   CREATE PROCEDURE pack1_name_get(@name VARCHAR(30) OUTPUT)
   AS
   BEGIN
     DECLARE @sql NVARCHAR(100)
     SET @sql = N'SELECT @name = name FROM ##pack1_vars_' + CAST(@@spid AS VARCHAR)
     EXEC sp_executesql @sql, @param = N'@name VARCHAR(30) OUTPUT', @name = @name OUTPUT
END

An example of procedure that uses package variable in SQL Server:

   CREATE PROCEDURE sp_use_pack1
   AS
   BEGIN
     DECLARE @name VARCHAR(30)
     EXEC pack1_init
 
     EXEC pack1_name_get @name OUTPUT
     PRINT @name
 
     EXEC pack1_name_set 'New name' 
   END

Advantages: Flexible solution, no need to change applications, data are cleaned automatically.
Disadvantages: More complicated dynamic SQL code, need to resolve conflicts between sessions by using unique table names.

• SQL Server White Papers, Articles and Reference