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.

Local 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

Result: Command(s) completed successfully

EXEC sp_use_tempt

Result: Msg 208, Level 16, State 1, Procedure sp_use_tempt, Line 6 Invalid object name '

temp1'

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

Global Temporary Tables

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

Result: Msg 208, Level 16, State 1, Line 1 Invalid object name '

temp1'

Temporary Table Limitations

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

Return: Msg 2772, Level 16, State 1 Cannot access temporary tables from within a function

Emulating Oracle Package Variables using Temporary Tables

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.