Teradata Temporary tables : Quick Example

There are three types of temporary tables in teradata .

  • Global
  • Volatile
  • Derived

Global temporary tables :

The scope of global temporary tables limited to the session only . when session ends  all the records are dropped but the definition or metadata remains in data dictionary .

it uses temp space .

 

Volatile temporary tables :

The Scope of volatile tables limited to the session . when session ends table is dropped . data dictionary is not used and

it uses spool space for data

 

Derived temporary tables :

The  derived tables has scope till the query execute  in the system. they are used to temporarily hold data .

 

 Example :

Creating global temporary table

Query: 

CREATE GLOBAL TEMPORARY TABLE EMP_TEMP, NO FALLBACK , NO LOG
(
EMPL_ID integer,
SAL decimal(10,2),
DEPT integer
) ;

Output : table created

 

Creating volatile temporary table

Query:

CREATE VOLATILE TABLE EMP_TEMP, NO FALLBACK , NO LOG
(
EMPL_ID integer,
SAL decimal(10,2),
DEPT integer
) ;

Output: table created

 

 Creating derived table :

Query:

Select ITEM_ID,RANK(ALL_SALE) from (Select ITEM_ID, sum(sales) from SALES_DATA group by ITEM_ID)

AS TABLE_DERIVED (ITEM_ID,ALL_SALE);

Output: table created

Share this post

Leave a Reply