What you need to know before creating a table in Teradata

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Junior Contributor

Re: What you need to know before creating a table in Teradata

The main difference between SET/MULTISET tables can be noticed when there's INSERT VALUES vs. INSERT SELECT:
INSERT VALUES never silently discards duplicate rows, but INSERT SELECT will if the table is SET.

I used this BTEQ-script in my trainings to show all the variations:

.SET ECHOREQ OFF
.SET TIMEMSG NONE

-- SET/NUPI
CREATE SET TABLE dropme(i INT, j INT) PRIMARY INDEX(i);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2802 Duplicate row error
INSERT INTO dropme SELECT * FROM dropme; -- *** No rows added.
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** No rows found.
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- SET/UPI
CREATE SET TABLE dropme(i INT, j INT) UNIQUE PRIMARY INDEX(i);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2801 Duplicate unique prime key error
INSERT INTO dropme SELECT * FROM dropme; -- No rows added.
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** No rows found.
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- SET/NUPI/USI
CREATE SET TABLE dropme(i INT, j INT ) PRIMARY INDEX(i), UNIQUE INDEX(j);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2803 Secondary index uniqueness violation
INSERT INTO dropme SELECT * FROM dropme; -- *** No rows added.
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** No rows found.
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- SET/UPI/USI
CREATE SET TABLE dropme(i INT, j INT) UNIQUE PRIMARY INDEX(i), UNIQUE INDEX(j);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2801 Duplicate unique prime key error
INSERT INTO dropme SELECT * FROM dropme; -- *** No rows added.
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** No rows found.
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- MULTISET/NUPI
CREATE MULTISET TABLE dropme(i INT, j INT) PRIMARY INDEX(i);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme SELECT * FROM dropme; -- *** 2 rows added.
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** 4 rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** No rows found.
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- MULTISET/UPI
CREATE MULTISET TABLE dropme(i INT, j INT) UNIQUE PRIMARY INDEX(i);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2801 Duplicate unique prime key error
INSERT INTO dropme SELECT * FROM dropme; -- *** Failure 2801 Duplicate unique prime key error
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** One row found. 2801
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- MULTISET/NUPI/USI
CREATE MULTISET TABLE dropme(i INT, j INT) PRIMARY INDEX(i), UNIQUE INDEX(j);
INSERT INTO dropme VALUES(1,1); -- *** One row added.
INSERT INTO dropme VALUES(1,1); -- *** Failure 2803 Secondary index uniqueness violation
INSERT INTO dropme SELECT * FROM dropme; -- *** Failure 2803 Secondary index uniqueness violation
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** Failure 9127 Index violations detected
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** One row found. 2803
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;

-- MULTISET/UPI/USI
CREATE MULTISET TABLE dropme(i INT, j INT) UNIQUE PRIMARY INDEX(i), UNIQUE INDEX(j);
INSERT INTO dropme VALUES(1,1);
INSERT INTO dropme VALUES(1,1); -- *** Failure 2801 Duplicate unique prime key error
INSERT INTO dropme SELECT * FROM dropme; -- *** Failure 2801 Duplicate unique prime key error
CREATE ERROR TABLE FOR dropme;
INSERT INTO dropme SELECT * FROM dropme LOGGING ALL ERRORS; -- *** No rows added.
SELECT etc_errorcode FROM et_dropme WHERE etc_errorcode <> 0; -- *** One row found. 2801
DROP ERROR TABLE FOR dropme;
DROP TABLE dropme;
Enthusiast

Re: What you need to know before creating a table in Teradata

Thanks to bwb and dnoeth for their helpful comments. I made a minor clarification to this article about FastLoad or the TPT Load Operator being the utilities that will discard duplicate rows without any notification when they're used to insert into a SET table.
Enthusiast

Re: What you need to know before creating a table in Teradata

Nicely done.
Enthusiast

Re: What you need to know before creating a table in Teradata

Nice explanation with example. However, I have a question on SET/NUPI performance. I feel SET/MULTISET plays the higher level than Index, do we still find any performance degrage due to SET/NUPI?
Enthusiast

Re: What you need to know before creating a table in Teradata

That is a very nice explanation dieter... you rock !!!!
Enthusiast

Re: What you need to know before creating a table in Teradata

With Dieter's wonderful explanation, one thing is clear that Teradata behaves differently when using INSERT INTO .... VALUES and INSERT INTO ... SELECT *...

Does anyone know why is this differentiation?
Enthusiast

Re: What you need to know before creating a table in Teradata

I also found that there is a difference on the identity column manipulation.... Thought we specified the start and end value along with increment count, it behaves differently. When we do Insert Into Values, it starts with 1 and incremented by 1 whereas when we do Insert Into ...Select, it starts with some 1000 or 10000 and incremented based on the increment value specified... don't know if there is any other dbc level setting for that.
Enthusiast

Re: What you need to know before creating a table in Teradata

Thank you jskarphol for article.
Is there any impact of choice of data type on selection of primary index?
Varchar, char or integer? Since hashed values will be stored what if any difference makes data type of primary index?
What do you think about adding surrogate keys as a column to all tables as ultimate candidate for primary index? I understand that users utilize natural keys for data selection but, those can be handled with secondary indexes.
Enthusiast

Re: What you need to know before creating a table in Teradata

Thank you,very informative