In this short post you will find out what is the difference between the SET and MULTISET table (Teradata multiset vs set table) and why you need to know the difference before creating your table.
The tables defined as SET primarily do not allow you store duplicate data. Teradata will also not allow you to create this type of table by defining it as NO PRIMARY INDEX. Let’s look at the following example:
CREATE SET TABLE STUDY ( STUDY_ID INTEGER, STUDY_NAME VARCHAR(10), STUDY_STATUS BYTEINT); INSERT INTO STUDY VALUES (1,'XYZ', 0);
Ok, we created the tables and put the first record in it. Let’s try to add a duplicate:
INSERT INTO STUDY VALUES (1,'XYZ', 0);
At this point you probably got the error “Duplicate row error in STUDY”. Duplicates in this table are not allowed! (Teradata multiset vs set table)
MULTISET tables allow you to store duplicates. Let’s define COUNTRY tables this time as MULTISET tables:
CREATE MULTISET TABLE COUNTRY ( COUNTRY_ID INTEGER, COUNTRY_NAME VARCHAR(50), COUNTRY_CODE VARCHAR(2)); INSERT INTO COUNTRY VALUES (1,'POLAND', 'PL');
And now let’s try to add a duplicate:
INSERT INTO COUNTRY VALUES (1,'POLAND', 'PL');
Table Type And Performance Impact
If you define your table as SET, every time you insert or update the data, Teradata checks it for duplicates. For small tables, this does not matter, but if the tables contain a lot of data, these operations will take much more time than the same operations on the MULTISET table.
Okay, but what if I want my table to store unique data?
The best answer to this question will be the unique primary index (UPI). (Teradata multiset vs set table)