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

Question: How is Oracle Temporary Table Different from SQL Server?

Answer: I recently heard this question in the interview where we were hiring the candidate for SQL Server and Oracle.

There are quite a few differences between Temp Tables of Oracle and SQL Server. However, the major difference which I believe is how they are implemented and consumed.

In SQL Server –

When we create temp tables, they are created for each session and dropped at the end of the each session. If we want to use the same table again, we will have to recreate them.

However, we can create global temporary tables which stay till we restart the SQL Server. (They can be accessed similar to Oracle’s temporary tables)

Here is the syntax for the temp table in SQL Server.

CREATE TABLE #TempTable (ID INT)

Here is the syntax for global temporary table in SQL Server.

CREATE TABLE ##TempTable (ID INT)

In Oracle –

When we create temporary tables, they are available globally all the time. When we close the sessions at the end of the session either the rows of the tables are preserved or deleted. In any case, the table remains there and is not needed recreate them.

Here is the syntax for temp tables in Oracle.

CREATE GLOBAL TEMPORARY TABLE TempTable (ID INT)
ON COMMIT DELETE ROWS;

or

CREATE GLOBAL TEMPORARY TABLE TempTable (ID INT)
ON COMMIT PRESERVE ROWS;

Reference: Pinal Dave (https://blog.sqlauthority.com)