Thursday, 8 September 2016

How to have Teradata determined primaryindex the right way

Teradata Primary Index
Teradata Primary Index
Every table must have at least one column as the Primary Index. The Primary Index is defined when the table is created.There are two reasons you might pick a different Primary Index then your Primary Key. They are (1) for Performance reasons and (2) known access paths.

Primary Index Rules

Rule 1: One Primary Index per table.
Rule 2: A Primary Index value can be unique or non-unique.
Rule 3: The Primary Index value can be NULL.
Rule 4: The Primary Index value can be modified.
Rule 5: The Primary Index of a populated table cannot be modified.
Rule 6: A Primary Index has a limit of 64 columns.
Two Types of Primary Indexes (UPI or NUPI)
Unique Primary Index(UPI)-A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column
Non-Unique Primary Index (NUPI)-A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.

What happens when user not defined Primary index:
  • Teradata assigns first column of table as Primary index, when not defined during creating a Table. 
  • But always the best practice is need to define primary index during Table creation time. Else, you will get UNIQUENESS violation errors, since the first column of table may not follow uniqueness rules.

No comments:

Post a Comment