Temporary Table in Teradata

Teradata provides the flexibility to use three types of temporary table which helps user to accomplish their work more easily. This kind of table is temporary to the database that means tables are not going to store permanently in the disk space, will be discarded after specific time based on type of table. Now we are going to discuss about each kind of table in details.

Derived Table(DT):-

The most frequently used temporary table is Derived table. It is created inside Users SQL and only exists for the life of the query. So whenever you are using any Select inside of FROM clause in SQL, Derived table is created automatically and its used for further processing. Derived table uses the users spool space and automatically released at the end of the query.

Example of Derive table-

SEL EMP_NAME,SALARY FROM    EMPLOYEE,(
   SEL    AVG(SALARY)
   FROM   EMPLOYEE) AS EMPLOYEE_TEMP(AVGSAL)
WHERE SALARY > AVGSAL
ORDER BY 2 DESC;

Here we want to know the employee name whose salary is greater than the average salary. From the above example, you can see that in the from clause, we have calculated average salary of employees. Here EMPLOYEE_TEMP will act a like a derived table. Please note that we need to mention clearly table name and column list clearly in derived table. 

Volatile table(VT):-

Volatile table can be created by any user and is populated with an INSERT-SEL statement. The characteristics of the volatile table is  that user can query in the volatile table until the user logs off. It also takes user’s SPOOL space and both table definition and data is deleted once the user logs off their session.

Example of Volatile table-

CREATE VOLATILE TABLE EMPLOYEE(   
  EMP_NO VARCHAR(10),   
  EMP_NAME VARCHAR(10),   
  SALARY INTEGER)
UNIQUE PRIMARY INDEX(EMP_NO)
ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS means to keep the data upon completion of the transaction.

We need to mention ON COMMIT PRESERVE rows explicitly as the default is ON COMMIT DELETE ROWS which delete the data from the table upon completion of the transaction.

Global Temporary Table(GTT):-

The global temporary table is created by any user who has TEMP space. The main difference between volatile table and derived table is, table definition will be stored in the Data Dictionary in case of GTT. Table definition will be available globally after the termination of the session also i.e. other user also can use this definition. Other user who has Temp Space can perform Insert-Select on the table and access their own data only.

Example of volatile table-

CREATE GLOBAL TEMPORARY TABLE MYDB.EMPLOYEE( 
   EMP_NO VARCHAR(10),
  EMP_NAME VARCHAR(10), 
  SALARY INTEGER)
UNIQUE PRIMARY INDEX(EMP_NO)
ON COMMIT PRESERVE ROWS;