Javatpoint Logo
Javatpoint Logo

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.

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

Example

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

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.

Example

Below query creates the Global Temporary table, such as:

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

A table has been created.





Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA