The difference between SET and MULTISET tables is?

The difference between SET and MULTISET tables is?

SET tables – SET tables did not allow duplicate values in the table.

If not specified in the DDL of the table then Teradata will create table as default SET. A SET table force Teradata to check for the duplicate rows every time a new row is inserted or updated in the table. This is an overhead on the resource if we need to insert massive amount of rows.

MULTISET tables – MULTISET tables allow duplicate values in table.

Remember that SET table causes an additional overhead of checking for the duplicate records. So we need to follow few points to save Teradata from this additional overhead.

·      If you are using any GROUP BY or QUALIFY statement on the source table then it’s highly recommended to define target table as MULTISET. As GROUP BY and QUALIFY will remove the duplicate records from the source.

·      If the source table has UPI (Unique Primary Index) then also there is no need of SET target table. As UPI will never allows duplicate PI in the same table.

So with the help of little bit of awareness about SET and MULTISET we can save a lot of time while loading the table.

If we are inserting data using INSERT into SEL from clause then SET table check for duplicate rows will removed automatically and there will be no DUPLICATE ROW ERROR.

·         If we are inserting data using INSERT into VALUES clause then SET table check for duplicate rows will not be removed automatically and there will be DUPLICATE ROW ERROR.