All about SET and MULTISET tables - Default Table type in Teradata - An example using Teradata Studio Express to explain all about SET and MULTISET tables

We generally say that tables are created as SET tables if the type is not mentioned in the CREATE TABLE statement.

The fact is, the type of the table is entirely dependent on the Transaction Mode.
In ANSI, the tables are created as MULTISET and in BTET (Teradata) mode, the tables are created as SET.

Example:

First select the transaction mode for the current session.

SELECT sessionno, transaction_mode FROM dbc.SessionInfoV
WHERE SessionNo in (select session);

Finding the Transaction mode for current Teradata session

create table financial.userset2

 (

 userid integer

 ) primary index(userid);

The above query gives a SET table in Teradata mode.

Note: If we don’t specify the primary index, table is created as multiset even in BTET mode.
If we try to create a SET table explicitly as a NoPI, we get an error in Teradata - "Cannot create a NoPI table as a SET table"

SET table with No Primary Index Error in Teradata

create table financial.userset3

 (

 userid integer

 ) no primary index;

 --- results in a multiset table if the mode is BTET as the PI is not defined

 show table financial.userset2;

 ---Insert row into set table

 insert into financial.userset2 values(1);

 ---- insert duplicate rows in multiset table

 insert into financial.userset3 values(2);

 insert into financial.userset3 values(2);

 insert into financial.userset3 values(2);

 ---- insert from multiset table to set table

 ---- error raised in ANSI mode but suppressed in Teradata mode

insert into financial.userset2 select * from financial.userset3;

---- only 2 rows, userid 1 and 2 should be present.

---- Note that no errors or warnings are raised

select * from financial.userset2;

Rows inserted into SET table without warnings in BTET mode

If we set the session to ANSI mode and then run the below query:

create table financial.users(

uid integer

,uname varchar(20)

)no primary index;

The table is created as multiset table.

show table financial.users;

---By default tables get created as multiset tables in ANSI mode

/*

CREATE MULTISET TABLE financial.users ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      uid INTEGER,

      uname VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC)

NO PRIMARY INDEX ;

*/