SQL Server / Oracle : Private temporary tables

Question

In Oracle, you can create a temp table using something like:

CREATE GLOBAL TEMPORARY TABLE temp_table (
    field1 NUMBER,
    field2 NUMBER
)
ON COMMIT DELETE ROWS;

... which could be pretty nifty, as this creates a table visible to everyone, but the data one INSERTs into the table is visible only to him or her. Additionally, that data is automatically deleted on at the end of the transaction or the session (depending on its declaration), leaving everyone else's temporary data unharmed.

In SQL Server, however, you can create a temp table with:

CREATE TABLE #temp_table (field1 INT, field2 INT);

... which, as I understand it, is substantially and functionally different than Oracle's implementation. This temp table is visible only to you, and is dropped (the table) immediately after use.

Is there any capacity in SQL Server to mimic the Oracle behavior as described above? Or is the only way to work with temporary data involve having to repeatedly CREATE the temp table with each iteration of work?

Answer 1

As you have discovered SQL Server & Oracle temporary tables are fundamentally different.

In Oracle global temporary tables are permanent objects that store temporary session specific (or transaction specific) data.

In SQL Server temporary tables are temporary objects storing temporary data, with #temp_tables storing data that is local to a session and ##temp_tables storing data that is global. (I have never had a need for SQL Server global temp tables and don't know what problem they solve.) If the #temp_table was created in a stored procedure it will be dropped when the stored procedure exits. Otherwise it will be dropped when the session closes.

And no, there really isn't a way to make SQL Server mimic Oracle. You could use a normal table with an extra column storing a session ID. But you wouldn't get the advantages of temp tables with respect to less logging. You'd have to manually delete the temp data. And deal with cleaning up from sessions that quit prematurely.

EDIT: Another difference between Oracle and SQL Server is that SQL Server allows DDL to be wrapped in a transaction with other statements. So if you need to use a temp table as part of a larger transaction, the create table #table_name... statement will not implicitly commit the current transaction like a create table statement would in Oracle.