Monday, December 19, 2011

What are the options that are not available for global temporary tables ?

GTT  definitions are created in Data dictionary.  These table provide separate instance to each user who refers to them .

The following options are not available for global temporary tables:
- Any kind of constraints like check/referential cannot be applied on table
- Identity columns since data in GTT are materialized only for session
- Permanent Journaling cannot be done as data in tables are instances only to user in that session
- PPI cannot be applied as data does not get stored in PERM , only TEMP space is utilized here.

What are the options not available for volatile tables in teradata ?

The following options are not available for volatile tables because table definition is not stored in data dictionary
- Default values for columns 
- Title clause for columns
- Named Indexes for table
- Compression on columns/table level since table data are spooled
- Stats cannot be collected since data is materialized only for session 
- Identity columns as these again would need entry in IDcol tables
- PPI cannot be done on tables
- Any kind of constraints like check/referential cannot be applied on table