Global Temporary Tables and Volatile Temporary Tables in Teradata

These are Teradata Specific concepts.
Both are temporary tables and are used normally as a kind of "intermediate stage" for an SQL operation.

Global Temporary Tables (GTT) :
1.GTT takes up space from Temporary space
2.GTT survive TD system restarts
3.Index can be created on GTT.
4.CHECK or BETWEEN constraints, COMPRESS column and DEFAULT
and TITLE clause are supported by GTT.
5.you can collect stats on GTT.
6.Table Definition is stored in DD for GTT.
7.In a single session 2000 Global temporary table can be materialized.
8.Materialized instance of GTT will be discarded at session end.

Volatile Temporary Tables (VTT):
1.VT takes up space from spool, doesn't have any info in DD.
so is slightly faster that GTT in that terms because no DD lookup is required.
2.VT can't survive TD system restarts
3.Index cann't be created on GTT.
4.CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are not supported by VT.
5.No collect stats for VTT.
6.Table Definition is stored in System cache for VTT.
7.In a single session 1000 Volatile tables can be materialized.
8.Volatile tables are always qualified by the session's userid.

This is how we usually create the GTT and VTT.
GTT :
CREATE GLOBAL TEMPORARY TABLE gt_deptsal
( deptno SMALLINT
,avgsal DEC(9,2)
,maxsal DEC(9,2)
,minsal DEC(9,2)
,sumsal DEC(9,2)
,empcnt SMALLINT);

VTT :

CREATE VOLATILE TABLE vt_deptsal, LOG
(deptno SMALLINT
,avgsal DEC(9,2)
,maxsal DEC(9,2)
,minsal DEC(9,2)
,sumsal DEC(9,2)
,empcnt SMALLINT)
ON COMMIT PRESERVE ROWS;


ON COMMIT PRESERVE ROWS allows us to use the Volatile table again for other queries in the session. The default statement is ON COMMIT DELETE ROWS, which means the data is
deleted when the query is committed.

INSERT INTO gt_deptsal
SELECT dept ,AVG(sal) ,MAX(sal) ,MIN(sal) ,SUM(sal) ,COUNT(emp)
FROM emp
GROUP BY 1;

Problem
To show all employees who make the maximum salary in their department.
Sol :
SELECT emp, last, dept, sal
FROM emp INNER JOIN gt_deptsal
ON dept=deptno
WHERE sal=maxsal
ORDER BY 3;