SET or MULTISET tables

We know that in Teradata all the tables are either SET or MULTISET.

The difference between SET and MULTISET tables is –


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

e.g.

COLUMN 1          COLUMN 2          COLUMN 3          COLUMN 4

A                            b                            c                            d

H                           g                            y                            k

A                            b                            c                            d             (Not allowed)

Syntax

CREATE SET TABLE <table_name> ………

………

MULTISET tables – MULTISET tables allow duplicate values in table.

e.g.

COLUMN 1          COLUMN 2          COLUMN 3          COLUMN 4

A                            b                            c                            d

H                           g                            y                            k

A                            b                            c                            d             (allowed)

Syntax

CREATE MULTISET TABLE <table_name>………

………

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.

Which table to choose?

Before creating the table it’s very important to know what kind of data is required in the table and based on that we must define SET or MULTISET.


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.


Key Points to remember.

• 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.