When creating tables in Teradata, it is important to consider whether or not you want to allow duplicate values in the table. The SET and MULTISET clauses are used in CREATE TABLE statement in Teradata with characteristics to allow duplicate in the table or NOT. If you specify SET, which adheres to relational data model, it will NOT allow duplicate rows in a table. On the other hand, MULTISET concept follows multidimensional data model and allows duplicate values in a table. By default, Teradata enforces a “no duplicates” rule for all standard CREATE TABLE statements.

Following two examples shows each of the above scenario.

CREATE SET TABLE DBNAME.TABLE_NAME ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

………

………

………

)

UNIQUE PRIMARY INDEX (COLUMN1 ,COLUMN2);

 

 

CREATE MULTISET TABLE DBNAME.TABLE_NAME, NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

………

………

………

)

UNIQUE PRIMARY INDEX (COLUMN1 ,COLUMN2);

Our Random Articles

More Links