Permanent Table :

 Once created, remains in the database until it is dropped. It can be shared with different sessions and users. A permanent table structure is stored in the DBC data dictionary database (DBC.Temptables).
  • SET Table: It is Permanent table that will not allow data duplicated in the table.
  • Multi SET Table: It is a Permanent table, which will allow duplicates in the table
  • Queue Table :
    • Are permanent tables with a timestamp column. The timestamp reflects when each row was inserted into the table.
    • Establish first-in-first-out (FIFO) ordering of table contents, which is needed for customer applications requiring event processing.
Global Temporary tables (GTT)  :
  • its definition stores in Data Dictionary.
  • Where materialized data stored in temp space.
  • The collection of stats is possible on GTT.
  • Syntax :  CREATE GLOBAL TEMPORARY TABLE { sql }
Volatile Temporary tables (VTT) 
  • Availability is local to a session. Once the session ends structure and data will not be available.
  • Table Definition is stored in System cache.
  • Data is stored in the spool space.
  • Data and Table definition will be active only up to the session ends.
  • The collection of stats is possible in latest version of TD.
  • Syntax: CREATE VOLATILE TABLE { sql statement }
Derived tables :

  • Derived tables are local to the query that means the table exists only for the duration of the query execution.
  • The table is automatically destructed once the query is done.
  • Spooled rows, which populate the table are also discarded when the query finishes
  • It is incorporated into SQL query syntax
  • There is no data dictionary involvement