Global Temporary tables in teradata

About GLOBAL TEMPORARY: 

You are working in a Teradata database application. 
You have to CREATE a table and insert some records for performance analysis. 
The table has several columns and you have to DELETE the content once you have done with your job. 
But you don't want to drop the table since it is being used daily for the analysis. 

What can be done for this? 

Teradata gives GLOBAL TEMPORARY option with CREATE TABLE syntax. 

What special ? 

If you create a table with GLOBAL TEMPORARY option, the life of the CONTENTS of the table will be only for the current session. 
They will be automatically deleted by Teradata manager once session expires. (Only the content not definition) 

Syntax: 

CREATE GLOBAL TEMPORARY TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
);

CREATE GLOBAL TEMPORARY TABLE employee
(
ID INT,
Name VARCHAR(20)
);

Interesting about GLOBAL TEMPORARY: 

Now, you are creating a GLOBAL TEMPORARY table and inserting some records successfully into it. 
SELECT * from the GLOBAL TEMPORARY table. Shocked ? ... 
Yes. You cannot see the records because Teradata becomes strict to GLOBAL TEMPORARY tables by incorporating ON COMMIT DELETE ROWS by default to add more meaning to TEMPORARY. 

so, if you want to see the records, you have to explicitly write ON COMMIT PRESERVE ROWS immediately after CREATE TABLE syntax. 

CREATE GLOBAL TEMPORARY TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
) ON COMMIT PRESERVE ROWS;

More about GLOBAL TEMPORARY: 

1. You can have 2000 volatile tables in an active session. 
2. REFERENTIAL constraints are not allowed. 



Here GLOBAL TEMPORARY says, DO and GO.. i will PRESERVE and take care... 

..

Volatile table in teradata

You are working in a Teradata database application. You have to CREATE a table and insert some records for performance analysis. You have to DROP the table once you have done with your job. If this is the case, will you think a smart way for this?

Yeah. Teradata gives VOLATILE option with CREATE TABLE syntax. 

What special ?

If you create table with VOLATILE option, the life of the table will be only for the current session.
It will be automatically dropped by Teradata manager once session expires. (Both definition and content)

Syntax:

/* Volatile Table Syntax - Teradata */
CREATE VOLATILE TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
);

CREATE VOLATILE TABLE employee
(
ID INT,
Name VARCHAR(20)
);

Interesting about VOLATILE:

Now, you are creating a VOLATILE table and inserting some records successfully into it.
SELECT * from the volatile table. Shocked ? ...
Yes. You cannot see the records because Teradata becomes strict to VOLATILE tables by incorporating ON COMMIT DELETE ROWS by default to add more meaning to VOLATILE.

so, if you want to see the records you have to explicitly write ON COMMIT PRESERVE ROWS immediately after CREATE TABLE syntax.

CREATE VOLATILE TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
) ON COMMIT PRESERVE ROWS;

More about VOLATILE:

1. You can have 1000 volatile tables in an active session.
2. CHECK and REFERENTIAL constraints are not allowed.
3. DEFAULT clause not allowed.


Here VOLATILE says, DO and GO.. i will take care...