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;