Tables are two-dimensional representation of data in rows & columns. Tables form the core of any RDBMS environment. In TERADATA, Table creation is not as straightforward as it looks. You can create Tables with minimal options as we have in any ANSI standard. However creating a table in Teradata gives you much more control over Table. The syntax for CREATING TABLE in TERADATA can be :
CREATE [SET/MULTISET] TABLE DATABASENAME.TABLENAME, [NO] FALLBACK, [NO] BEFORE JOURNAL, [NO] AFTER JOURNAL, ( COLUMN1 DATATYPE, COLUMN2 DATATYPE, . . . COLUMNZ DATATYPE ) [UNIQUE] PRIMARY INDEX [INDEXNAME] (COLUMN1,COLUMN2...COLUMNZ) [UNIQUE] INDEX [INDEXNAME] (COLUMN1.COLUMN2...COLUMNG)
Now let us see what each of these options actually mean:
[SET/MULTISET]: SET tables discard completely duplicate records however MULTISET allows multiple instance of rows. So it depends on the situation. If you know that you will be getting distinct rows , it is advisable to go with MULTISET table as it will not check for duplicate rows. Hence, saving time and enhancing performance. However if you know that you would be getting duplicate records however you need to process only one of the duplicates rows, Go with SET tables. TERADATA default is SET however ANSI default is MULTISET.
FALLBACK: FALLBACK is TERADATA mechanism to save table data in case of AMP Failure. If you define any table with FALLBACK option , a duplicate copy of table data is maintained in some other AMP. SO in case of AMP Failure, the FALLBACK AMP is used. For critical tables, it is recommended to use FALLBACK option. However it also comes with more Storage Space utilisation disadvantage. In TERADATA, default is NO FALLBACK.
PERMANENT JOURNALS:There are four types of Permanent Journals you can use: BEFORE, AFTER, DUAL BEFORE, DUAL AFTER. The BEFORE JOURNAL holds the image of impacted rows before any changes are made. AFTER JOURNAL holds the image of affected rows after changes are done. In DUAL BEFORE/AFTER Journal, two images are taken and are stored in two different AMP’s.
As it was also discussed earlier that PERMANENT JOURNALS use PERMANENT SPACE so if these JOURNALS are not required any more, it is advisable to drop JOURNALS to save some space. Also, there are several methods of creating tables in TERADATA. The most basic method is shown above. However if you want to CREATE TABLE using some other table then you can use below command:
CREATE TABLE TABLE_1 AS (SELECT * FROM TABLE_2) WITH [DATA/NO DATA] AND [STATS/NO STATS];