Temporary tables

Well, there's been a lot said about temporary tables in Oracle and SQL Server.

Although Oracle is closing in with the concept of Private Temporary Tables in 18c SQL Server idea is still much easier to use. In Oracle you'll need to use some weird prefix (ora$ptt_) in addition to the statement itself. On the plus side, in Oracle, you may benefit from ON COMMIT additional option allowing you to control data between transactions. On the other hand, in SQL Server you may share data between sessions which is not possible in Oracle when using temporary tables. You would need to use traditional table for that.

So what options do we have in both RDBMS engines?

In SQL Server we have a session temporary table. It's gonna disappear with the session and it doesn't share data with other sessions.

select * 
into #dataset
from (
    select 2 as x union all
    select 4 as x union all
    select 1 as x union all
    select 3 as x
) dataset

select * from #dataset

(4 rows affected)
x
-----------
2
4
1
3

(4 rows affected)

We can allow other sessions to use our temporary table by prefixing it with another # sign, like so:

select * 
into ##dataset
from (
    select 2 as x union all
    select 4 as x union all
    select 1 as x union all
    select 3 as x
) dataset

select * from ##dataset

How it is in Oracle? Very different, especially when versions prior to 18c are considered. In those versions (8i+) you have to use something called Global Temporary Table. In this concept, the table definition is permanently present in the database and what is really temporary is the data. You could possibly drop and create such tables but this requires certain privileges which, very often, are limited to certain users. It's easier (especially when you're doing work in heavily regulated environments like banking, insurance, pharma, government, military, etc.) to assume you can't do much DDL on the fly.
Data however, is temporary when those tables are considered and is never shared between sessions. As far as transactions go (yeah, weird concept for SQL Server developers :P) you can set the table to clean up on commit or just on session termination. This, however, is a table option so...

I'm not going to go into much detail, just browse into the links.

create global temporary table GT_DATASET (
    x NUMBER
)
ON COMMIT PRESERVE ROWS;

insert into GT_DATASET ( x )
select x 
from (
        select 2 as x from dual union all
        select 4 as x from dual union all
        select 1 as x from dual union all
        select 3 as x from dual
);

select * from GT_DATASET;

Global temporary TABLE created.


4 rows inserted.


         X
----------
         2
         4
         1
         3
Like I said before, it's only Oracle 18c that has introduced the concept of a really temporary table - Private Temporary Table. This table will disappear once the session ends (always) or when transaction commits (I suspect rollback will also remove the table) - depending on ON COMMIT parameter.

Sorry guys, can't provide you with any examples of my own - don't have access to any 18c at the moment but you can find all the details on above linked pages.