How is Oracle Temporary Table Different from SQL Server? – Interview Question of the Week #133

Interview question: How is an Oracle global temporary table different from a SQL Server temporary table?

Answer: In Oracle, the global temporary table definition is a persistent database object visible to authorized sessions, while each session’s rows are private. In SQL Server, a local #temp table is created for a session and normally disappears when that session ends. SQL Server’s global ##temp table is visible across sessions, including its rows, so it is not the same as Oracle’s global temporary table.

I heard this question while interviewing someone for a role spanning SQL Server and Oracle. The word global is the trap: it refers to Oracle’s shared definition, not shared row contents.

A SQL Server session can create and use a local temporary table like this:

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
    THROW 50136, 'Demo temporary table exists. Open a fresh query window.', 1;

CREATE TABLE #TempTable (ID int);
INSERT INTO #TempTable (ID) VALUES (1);
SELECT ID FROM #TempTable;
DROP TABLE #TempTable;

Oracle’s global temporary table is usually defined once, before the sessions that use it. Choose whether each session loses its rows at commit or keeps them until the session ends:

-- Oracle syntax, not Transact-SQL
CREATE GLOBAL TEMPORARY TABLE TempTable (ID NUMBER)
ON COMMIT DELETE ROWS;

-- Alternative definition for a different table:
CREATE GLOBAL TEMPORARY TABLE TempTableSession (ID NUMBER)
ON COMMIT PRESERVE ROWS;

The old post said a SQL Server ## table stays until server restart. That is not the normal default: with GLOBAL_TEMPORARY_TABLE_AUTO_DROP on, it is dropped after the creating session ends and other active statements stop referencing it. Other sessions can also see its rows, unlike Oracle’s per-session row contents.

Oracle also has private temporary tables in modern versions. This interview answer compares the Oracle global temporary table in the original question with SQL Server’s familiar # table; do not generalize it to every temporary-table feature in either product.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.