Teradata Table Types

Teradata supports the following types of tables to hold temporary data.

S. No.	Table Types	Description
1	ANSI Temporal	ANSI-compliant support for temporal tables. Using temporal tables, Teradata Database can process statements and queries that include time-based reasoning. Temporal tables record both system time (the time when the information was recorded in the database) and valid time (when the information is in effect or correct in a real-world application).
2	Derived	A derived table is a type of temporary table obtained from one or more other tables as the result of a SubQuery. It is specified in an SQL SELECT statement. It avoids the need to use the CREATE and DROP TABLE statements for storing retrieved information. It is useful when we are more sophisticated and complex query codes.
3	Error Logging	Error logging tables store the information about errors on an associated permanent table. And it also stores Log information about insert and updates errors.
4	Global Temporary	Global temporary tables are private to the session. It is dropped automatically at the end of a session. It has a persistent table definition stored in the Data Dictionary. The saved description may be shared by multiple users and sessions, with each session getting its instance of the table.
5	Global Temporary Trace	Global temporary trace tables are store trace output for the length of the session. It has a persistent table definition stored in the Data Dictionary. It is useful for debugging SQL stored procedures (via a call to an external stored procedure written to the trace output) and external routines (UDFs, UDMs, and external stored procedures).
6	NoPI	NoPI tables are permanent tables that do not have primary indexes defined on them. They provide a performance advantage when used as staging tables to load data from FastLoad or TPump Array INSERT. They can have secondary indexes defined on them to avoid full-table scans during row access.
7	Permanent	Permanent tables allow different sessions and users to share table content.
8	Queue	Queue tables are permanent tables with a timestamp column. The timestamp indicates when each row was inserted into the table. It is established first-in-first-out (FIFO) ordering of table contents, which is needed for customer applications requiring event processing.
9	Volatile	Volatile tables are used only when One session needs the table. The creator needs to access the table. We want better performance than a global temporary table. We do not need the table definition after the session ends. Note: The definition of a volatile table can survive across a system restart if it is contained in a macro.

Derived Table

Derived tables are created, used, and dropped within a query. These are used to store intermediate results within a query.

Example

Consider the following employee record in the form of two tables.

Emp Table:

Employee_Id	First_Name	Last_Name	Department_No
202001	Mike	Richard	1
202002	Robert	Williams	2
202003	Peter	Collin	2
202004	Alexa	Stuart	1
202005	Robert	Peterson	1

Salary Table:

Employee_Id	First_Name	Last_Name	Department_No
202001	40,000	4,000	36,000
202002	80,000	6,000	74,000
202003	90,000	7,000	83,000
202004	75,000	5,000	70,000
202005	80,000	00	80,000

The following query creates a derived table EmpSal with records of employees with a salary higher than 80,000.

SELECT 
Emp.Employee_Id, 
Emp.First_Name, 
EmpSal.NetPay 
FROM 
Emp, 
(select Employee_Id , NetPay 
from Salary
where NetPay >= 80000) EmpSal 
where Emp.Employee_Id = EmpSal.Employee_Id;

When the above query is executed, it returns the employee's records with a salary higher than or equal to 80,000.

Employee_Id       First_Name       NetPay
202003            Peter            83,000
202005            Robert           80,000

Volatile Table

Volatile tables are created and dropped within a user session. Their definition is not stored in the data dictionary. They hold intermediate data of the query, which is frequently used.

Syntax

CREATE [SET|MULTISET] VOALTILE TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 
ON COMMIT [DELETE|PRESERVE] ROWS

Example

In the following example, we create a VOLATILE table that name is dept_stat.

CREATE VOLATILE TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no) 
ON COMMIT PRESERVE ROWS;

After executing the above code, it returns the min, avg, and max salary according to the departments in the output.

Dept_no     avg_salary     max_salary     min_salary 
 1           186,000         80,000         36,000
 2           157,000         83,000         74,000

Global Temporary Table

The definition of the Global Temporary table is stored in the data dictionary, and they can be used by many users and sessions. But the data loaded into a global temporary table is retained only during the session.

We can materialize up to 2000 global temporary tables per session.

Syntax

Following is the syntax of the Global Temporary table.

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 

Example

Below query creates the Global Temporary table, such as:

CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no);

When the above query is executed, it returns the following output.