There are four types of tables exists per data storage in Teradata namely Permanent Tables, Global Temporary Tables (GTT), Volatile Tables & Derived Tables

Teradata Table Types

Four types of tables exists per data storage in Teradata
  • Permanent Tables
  • Global Temporary Tables (GTT)
  • Volatile Tables
  • Derived Tables
➠ Permanent Tables
  • As name suggest,these are permanent tables and remain in the system until it is dropped.
  • Table definition is stored in data dictionary.
  • Data & structure can be shared across multiple sessions and users.
  • Data is stored in the permanent space.
  • Collect statistics supported.
  • Indexes can be created.
  • COMPRESS column,DEFAULT and TITLE clause are supported.
  • Partition Primary Index(PPI) is supported
  • If primary index clause is not specified in create table,then Teradata will create first column as primary by default.
    Syntax/Example 1: 
    
CREATE MULTISET TABLE [databasename].[tablename]
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 );

    Syntax/Example 2: Permanent table with SELECT along with data 
    
CREATE MULTISET TABLE tutorial_db.permanent_employee_table
AS 
(   
 SELECT * FROM tutorial_db.employee
)  
WITH DATA; 

Note: 1. 'WITH DATA' clause will create table along with data.

    Syntax/Example 3: Permanent table with NO PRIMARY INDEX (NOPI) 
    
CREATE MULTISET TABLE tutorial_db.permanent_NO_PI_employee_table
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
NO PRIMARY INDEX; 

Note: 1. MULTISET tables only support NO PRIMARY INDEX (NOPI).
      2. Can be used as staging tables for load utilities such as TPT load/Fastload and then select/insert into main table.

    Syntax/Example 4: Permanent table using other table with no data but statistics of base table 
    
CREATE MULTISET TABLE tutorial_db.permanent_employee_table
AS  tutorial_db.employee 
WITH NO DATA AND STATISTICS; 

Note: 1. 'WITH NO DATA' clause will create table structure only with no data.
      2. 'STATISTICS' clause will copy base table statistics

    Syntax/Example 5: Permanent table using other table with different primary index than base table 
    
CREATE MULTISET TABLE tutorial_db.permanent_employee_table
AS  tutorial_db.employee 
WITH DATA 
PRIMARY INDEX(emp_name);

    Syntax/Example 6: Finding permanent table size 
    
SELECT DatabaseName,TableName, SUM(CurrentPerm)/(1024*1024*1024) "TableSize in GB"
FROM DBC.TableSize 
WHERE DatabaseName = '[databasename]' 
AND TableName      = '[tablename]' 
GROUP BY DatabaseName,TableName ;


➠ Global Temporary Tables(GTT)
  • This is one kind of temporary tables.
  • Table definition is stored in data dictionary.
  • Requires a minimum of 512 bytes from the PERM space to store table definition in data dictionary.
  • Structure can be shared across multiple users but data will remain private to the session(and its user).
  • Data is stored in the temporary space.
  • Data will be purged for that session once session ends.
  • Collect statistics supported.
  • Indexes can be created.
  • COMPRESS column, DEFAULT and TITLE clause are supported.
  • Partition Primary Index(PPI) is supported
  • Can be identified from data dictionary table (dbc.tables) using 'CommitOpt' column,if its value is 'D' (on commit Delete rows) or 'P' (on commit Preserve rows) then it's a GTT.
  • If primary index clause is not specified in create table,then Teradata will create first column as primary by default.
  • One session can materialize up to 2000 global temporary tables at one time.
    Syntax/Example 7: On commit preserve rows 
    
CREATE MULTISET GLOBAL TEMPORARY TABLE [databasename].[tablename] 
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 )
ON COMMIT PRESERVE ROWS;

    Syntax/Example 8: On commit delete rows 
    
CREATE MULTISET GLOBAL TEMPORARY TABLE [databasename].[tablename] 
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 )
ON COMMIT DELETE ROWS; 

Note:  ** This GTT can be used in between BT/ET (Begin Transaction/End Transaction) because data will be purged after each commit


➠ Volatile Tables
  • This is one kind of temporary tables.
  • Table definition is not stored in data dictionary.
  • Structure & data is private to the session and its user.
  • Data is stored in the spool space.
  • Table gets dropped once session ends.
  • Collect statistics supported.
  • Indexes can not be created.
  • COMPRESS column is supported but DEFAULT and TITLE clause are not supported.
  • Partition Primary Index(PPI) is supported
  • If primary index clause is not specified in create table,then Teradata will create first column as primary by default.
  • All the volatile tables in current session can be check using below command: 
    
HELP VOLATILE TABLE;

    Syntax/Example 9: 
    
CREATE MULTISET VOLATILE TABLE [volatile_table_name]
(   
  col_1 INTEGER,
  col_2 VARCHAR(50)
) 
ON COMMIT PRESERVE ROWS; 

Note: ** Similar to GTT,volatile table can also be 'ON COMMIT DELETE ROWS'

    Syntax/Example 10: Volatile table with SELECT 
    
CREATE MULTISET VOLATILE TABLE volatile_employee_table
AS 
(   
 SELECT * FROM tutorial_db.employee
)  
WITH DATA
ON COMMIT PRESERVE ROWS; 

Note: ** Similar to GTT,volatile table can also be 'ON COMMIT DELETE ROWS'


➠ Derived Tables
  • This is one kind of temporary table that is derived from one or more other tables as the result of a sub-query.
  • Derived tables are local to the query and exists only for the duration of the query.
  • The table is automatically discarded once the query is done.
  • Data is stored in the spool space.
  • Table definition is not stored in data dictionary.
  • First column in the Derived table acts like PI column for the derived table
    Syntax/Example 11: Sub query named 'Derived_table' is the example of derived table 
    
SELECT employee.emp_name, employee.dept_no, employee.Salary, derived_table.Avgsal 
FROM (SELECT dept_no, AVG(salary) Avgsal
      FROM employee GROUP BY dept_no)
   AS derived_table),
Employee  
WHERE Derived_table.dept_no = employee.dept_no
AND employee.salary > derived_table.Avgsal;