DWHPro Insights

Teradata SET vs MULTISET Tables: Duplicates and Performance

By Roland Wenzlofsky ·

Teradata SET vs MULTISET Tables: Duplicates and Performance

A Teradata SET table does not allow duplicate rows; a MULTISET table can allow them. Unique indexes and constraints still apply to MULTISET tables. Choose the table type explicitly and define business-key rules separately from whole-row duplicate handling.

Watch: why a SET table drops rows without an error

This video walks through the lab example above: three rows in, two rows stored, no error. It then shows when the same insert fails instead, what the duplicate-row check costs, and how load tools treat duplicates.

Verified example: three input rows become two SET rows

Lab verification: 22 October 2026. The example below was executed on Teradata Database 20.00.28.81 using BTEQ 20.00.00.11 in Teradata (BTET) transaction mode. Each SQL statement was submitted as a separate request. These are small functional checks, not performance benchmarks; results involving transaction boundaries can differ in ANSI mode or an explicit transaction.

CREATE MULTISET VOLATILE TABLE dwhpro_ai_source
(id INTEGER, label VARCHAR(12))
PRIMARY INDEX (id) ON COMMIT PRESERVE ROWS;

INSERT INTO dwhpro_ai_source VALUES (1, 'alpha');
INSERT INTO dwhpro_ai_source VALUES (1, 'alpha');
INSERT INTO dwhpro_ai_source VALUES (1, 'beta');

CREATE SET VOLATILE TABLE dwhpro_ai_set
(id INTEGER, label VARCHAR(12))
PRIMARY INDEX (id) ON COMMIT PRESERVE ROWS;

INSERT INTO dwhpro_ai_set SELECT * FROM dwhpro_ai_source;
SELECT * FROM dwhpro_ai_set ORDER BY id, label;

DROP TABLE dwhpro_ai_set;
DROP TABLE dwhpro_ai_source;
TableObserved rowsWhat it demonstrates
MULTISET source3Both copies of (1, alpha) were stored.
SET target2: (1, alpha) and (1, beta)INSERT … SELECT removed the exact duplicate in BTET mode, while retaining two rows with the same id.

SET enforces uniqueness of the entire row, not uniqueness of id. This run does not demonstrate ANSI-mode behaviour or prove that one table type is faster. Use session-local volatile tables to reproduce it, and check target duplicate rules before copying data into another table.

SET versus MULTISET at a glance

QuestionSETMULTISET
Can identical rows be stored?No.Yes, unless a uniqueness rule prevents it.
Can a business key occur more than once?Yes, if other column values differ and no key constraint prevents it.Yes, unless a key constraint prevents it.
Does PRIMARY INDEX mean unique?Only when explicitly declared UNIQUE.Only when explicitly declared UNIQUE.
Duplicate-row checkingMay add work when uniqueness is not otherwise guaranteed.No SET-style whole-row duplicate check; constraints still require enforcement.

A nonunique primary index controls distribution and access; it is not a business-key constraint. Two rows with the same customer ID but different names are not identical rows, so a SET table alone does not enforce one row per customer.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Create SET and MULTISET tables explicitly

These volatile tables exist only in your current session. Use unused names. In ANSI mode, commit successful setup statements as required by your client before continuing.

CREATE SET VOLATILE TABLE demo_set
(
    customer_id INTEGER,
    customer_name VARCHAR(40)
) PRIMARY INDEX (customer_id)
ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE demo_multiset
(
    customer_id INTEGER,
    customer_name VARCHAR(40)
) PRIMARY INDEX (customer_id)
ON COMMIT PRESERVE ROWS;

Test exact duplicates separately from key duplicates

INSERT INTO demo_multiset VALUES (1, 'Alice');
INSERT INTO demo_multiset VALUES (1, 'Alice');
INSERT INTO demo_multiset VALUES (1, 'Alicia');

SELECT customer_id, customer_name, COUNT(*) AS copies
FROM demo_multiset
GROUP BY customer_id, customer_name;

Expected groups: (1, Alice, 2) and (1, Alicia, 1). This MULTISET table has no unique constraint.

INSERT INTO demo_set VALUES (1, 'Alice');
INSERT INTO demo_set VALUES (1, 'Alicia');

Both rows are allowed because they differ in customer_name. Run the next statement separately: it intentionally attempts an exact duplicate and should return an error.

INSERT INTO demo_set VALUES (1, 'Alice');

Duplicate handling in ANSI and Teradata modes

Do not assume duplicate rows are always silently removed. For INSERT … SELECT into a SET table, Teradata documents these different outcomes:

Target and sessionDuplicate-row outcome
SET, ANSI session modeThe request is rejected with an error.
SET, Teradata session modeDuplicate rows are rejected, nonduplicate rows are inserted, and no duplicate-row error is returned.
MULTISET without unique constraintsDuplicate rows are inserted.
MULTISET with a violated uniqueness ruleAn error is returned.

For a direct INSERT … VALUES into a SET table, an exact duplicate produces an error. Other constraint violations can be detected before a duplicate-row violation. Check the actual session mode when diagnosing different results between clients.

Check an existing table definition

Use SHOW TABLE to inspect the authoritative DDL. Replace the example database and table names:

SHOW TABLE YourDatabase.YourTable;

For an inventory of ordinary tables, the CheckOpt field in DBC.TablesV identifies SET versus MULTISET semantics:

SELECT DatabaseName, TableName,
       CASE CheckOpt
           WHEN 'N' THEN 'SET'
           WHEN 'Y' THEN 'MULTISET'
           ELSE CheckOpt
       END AS table_semantics
FROM DBC.TablesV
WHERE TableKind = 'T'
  AND DatabaseName = 'YourDatabase'
ORDER BY TableName;

Dictionary access depends on your privileges. Confirm a specific object with SHOW TABLE, especially when working with specialized table types.

Why duplicate-row checks can affect load performance

A SET table with many rows sharing a nonunique primary-index value can require substantial duplicate-row checking. The cost depends on the data, access structures, and load method. A unique index or constraint that already guarantees uniqueness can avoid the need for the same whole-row comparison work. This is not evidence that every MULTISET table is faster.

An earlier DWHPro example on this page reported 20.15 CPU seconds and 126,827 I/Os for SET, versus 0.96 CPU seconds and 3,556 I/Os for MULTISET. It used randomized input and 500 possible primary-index values. Treat those numbers as a historical illustration, not a controlled benchmark or a forecast for your system.

For a useful comparison, load the same fixed input into both definitions, record the session mode and load tool, control other workload, and compare row counts, elapsed time, CPU, I/O, and skew. Do not use random input generated independently for each table to claim a precise speedup.

Loading tools and NoPI tables

The target table definition is only part of the duplicate-handling behavior. FastLoad and Teradata Parallel Transporter Load have their own duplicate-row rules; review the operator and target-table documentation before assuming a MULTISET target preserves every input row. MultiLoad is not the only way to load a SET table.

Omitting a PRIMARY INDEX clause does not reliably request a NoPI table; configuration and other DDL clauses can affect the default. Request NO PRIMARY INDEX explicitly when that is your design:

CREATE MULTISET TABLE stage_customer
(
    customer_id INTEGER,
    customer_name VARCHAR(40)
) NO PRIMARY INDEX;

This permanent-table example requires CREATE TABLE privileges and database space. NoPI is a separate physical-design choice; evaluate it against the intended load and query workload.

Which should you choose?

  • Use MULTISET when duplicate input rows must be retained for staging, auditing, or later validation. Add the business-key constraints your model requires.
  • Use SET when whole-row uniqueness is an intentional requirement and its insert behavior and performance are acceptable.
  • Make deduplication rules explicit. SELECT DISTINCT removes identical selected rows; choosing the latest row per business key requires a separate, deterministic rule.
  • Specify SET or MULTISET in DDL. For ordinary tables, the default is SET in Teradata session mode and MULTISET in ANSI mode; explicit declarations make deployments easier to review.

Changing an existing table

Plan a rebuild into an explicitly defined target when changing table semantics, and confirm the supported DDL for your release. Check duplicate and key rules first, copy and reconcile the data, recreate the required statistics and access structures, and plan dependent-object and application cutover. Keep the original until the replacement has passed validation.

Official references

Reproduce the lab checks

Download the SQL used for these functional checks. Run it in a separate BTET lab session with unused dwhpro_ai_* table names. It creates and drops only its own volatile tables and reads DBC metadata; it contains no login credentials. Execute the statements separately, as in the test. The file covers date formatting, duplicate rows, commit behaviour, table copying and spool counters.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

13 thoughts on “Teradata SET vs MULTISET Tables: Duplicates and Performance”

  1. If we have a NUPI , and Table is defined as SET table , In this scenario Row Check will happened or Not when row is not unique .

    Reply
  2. Hello Roland,
    If I have a SET table with UPI and you write:
    “However, the performance of the SET table is not degraded when the UPI (Unique Primary Index) is defined for the table. Because the UPI itself guarantees uniqueness, the DUPLICATE ROW CHECK check is not performed.”

    However, duplicate rows ( inserted by IaS ) were discarded (silently) similarly to DUPLICATE ROW CHECK and only non-duplicate rows were inserted.
    (This behavior differs from the behavior of the MULTISET table with UPI, where a reported error (INSERT Failed 2801) and of course no rows were inserted.)

    You could clarify the difference between (and with respect to the above) your statement
    1. “… For commands INSERT INTO SELECT * FROM , duplicate rows are automatically for SET tables (no error) …” – I mean particulary ‘automatically’.
    and
    2. DUPLICATE ROW CHECK for the SET table?

    Thank you in advance
    Henri

    Reply
  3. This is why we have SET tables (Does NOT allow duplicate rows) and MULTISET tables (row duplicates allowed) on Teradata.

    Reply
  4. Small correction to my first comment.

    SELECT * FROM DBC.TABLES WHERE checkopt = ‘N’ AND TABLEKIND = ‘T’; — Set Tables
    Comment: Checkopt= ‘N’ is possible for MULTISET tables also.If the table is MULTISET with Unique index(UPI or USI) then Checkopt=’N’.

    SELECT * FROM DBC.TABLES WHERE checkopt = ‘Y’ AND TABLEKIND = ‘T’; — Multiset tables
    Comment: Checkopt=’Y’ means tables is always MULTISET but not in reverse order(Vice Varsa).

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam