CREATE TABLE with UNIQUE PRIMARY INDEX in Teradata

PRIMARY INDEX is mandatory for any table in Teradata.
While creating a table in Teradata, Teradata will look for any primary index specified in the DDL.
If the index is not specified, secondly it will look for Primary key constraint in DDL for making it primary index.
If the above conditions are not met then Teradata will take the first column as a Primary index.
It is recommended that we should explicitly specify the PRIMARY INDEX for a column in a table since we can assign a proper field for a primary index that will help Teradata to spread the data across the amps efficiently.

    CREATE SET TABLE tbl_employee
    ( 
    EmpID INT,
    EmpName VARCHAR(20)
    )
    UNIQUE PRIMARY INDEX(EmpID);    


For a SET table, it is advised to use UNIQUE PRIMARY INDEX since it will not allow duplicate rows.
UNIQUE PRIMARY INDEX will help the SET table to check for duplicates easily rather than comparing entire row under its inspection.

Example for composite unique primary index
CREATE TABLE tbl_employee
(
name VARCHAR(20),
id INT,
roll_no INT
)
UNIQUE PRIMARY INDEX (id, roll_no)


It is better to give UNIQUE PRIMARY INDEX for SET tables, since the duplicate records check is performed by the UPI rather than doing by SET.
So it improves the performance while inserting records.

CREATE SET TABLE tbl_employee
(
name VARCHAR(20),
id INT,
roll_no INT
)
PRIMARY INDEX (id, roll_no)


The above code requires attention since we specified only PRIMARY INDEX.
Here, the PRIMARY INDEX allows user to enter duplicate entries, thus the burden is for SET.
It needs to check each record for uniqueness before inserting into table.
The operation degrades the performance absolutely since SET needs to check entire columns.