Temporary table

When we are performing complex Joins/Operations, it’s always advised to load the data into temporary tables for better performance, temporary tables are used frequently in SQL Server but less frequently in Oracle, as oracle have Collection Types which are much robust and acts like temporary tables, for more info see this oracle doc.

 

SQL Server:

 

SQL server has two types of temporary tables

·         Global temporary tables (double hash table) – data and table is available to everyone connected to server

·         Session temporary table (single hash table)– data and table is available to just the session and when the session expires the temporary table no longer exists

 

Below example uses session temp table

 

--if exists drop the temp table

IF OBJECT_ID('TEMPDB..#CTE_NAME') IS NOT NULL

BEGIN

DROP TABLE #CTE_NAME

END

 --create a temp table

CREATE TABLE #CTE_NAME(COL1 INT, COL2 VARCHAR(10))

--insert into the temp table

INSERT INTO #CTE_NAME

VALUES (1,'SAM')

                         ,(2,'PAT')

             ,(3,'JON')

--now select to see the output

SELECT * FROM #CTE_NAME

 

Below example uses global temp table

 

--if exists drop the temp table

IF OBJECT_ID('TEMPDB..##CTE_NAME') IS NOT NULL

BEGIN

DROP TABLE ##CTE_NAME

END

 --create a temp table

CREATE TABLE ##CTE_NAME(COL1 INT, COL2 VARCHAR(10))

--insert into the temp table

INSERT INTO ##CTE_NAME

VALUES (1,'SAM')

                         ,(2,'PAT')

             ,(3,'JON')

--now select to see the output

SELECT * FROM ##CTE_NAME

 

 

Oracle:

 

Oracle just has global temporary table and once created the table is visible to everyone connected to server but the data inserted into the table is only available to the user who inserted it i.e. if two users insert data into the temporary table and if they retrieve the data they only get back the data which they have inserted.

 

Note: create or replace statement does not work for temporary tables and It’s a bad idea to drop and recreate global temporary table in oracle.

 

--create a temp table, there are 2 options when creating temp table "ON COMMIT DELETE ROWS" and "ON COMMIT PRESERVE ROWS"

CREATE GLOBAL TEMPORARY TABLE CTE_NAME(COL1 NUMBER, COL2 VARCHAR2(10)) ON COMMIT DELETE ROWS;

--insert into the temp table

INSERT INTO CTE_NAME VALUES (1,'SAM');

INSERT INTO CTE_NAME VALUES (2,'PAT');

INSERT INTO CTE_NAME VALUES (3,'JON');

--now select to see the output

SELECT * FROM CTE_NAME;

 

Dropping the temp table is similar to regular table i.e. using a drop table statement

 

            DROP TABLE CTE_NAME;

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.