UPI[Unique primary index]

Unique primary index takes only unique values and one null value

It distribute a data across all Amp’s uniformly(or) randomly(or) evenly, so this distribution is called uniform distribution(or) event distribution

*One Amp operation operate only one row.

Nupi[Non- unique primary index]

It takes duplicates and nulls

It’s distribution data across Amp’s proportional to the of uniqueness that means, it distributes less even distribution (or) unable distribution[duplicate row going to the sam Amp]

This distribution can also be called as Skewed distortion and the less uniform less is “Skewness”

*One Amp operation and effects many rows

PPI[Portioned primary Index]


Desired to gain proficiency on Teradata? Explore the blog post on Online Teradata Training 

to become a pro in Teradata.

NPPI[Non- partitioned primary Index]


Partitioned primary Index on P code[PPI]


Data partitioned based on pcode first

Within the partitioned data stored on row hash sequence.

We go for PPI generally when we using other then PI Columns use in the where clause along with PI frequency

If other column range based column query than also it’s recommended

In the above select query the system goes to the corresponding partitioned and skips the other partition so improves the performance.

Generally it is single Amp for some time, multi Amp operations also


Based on ’the condition’ we create partitions


Based on Range it creates partitions


no case:- Condition Un- matched data. Move in to the no case partition

Creation and dropping

PI Created only at table create


Create table<table name>(<columns>) Unique primary index(column names);


Create table<Table name>(< columns >) primary index(column names);


Create table<table name>(<columns>)primary index(columns) partition by column name;


Create set table test1


Party Id integer;

Party name varchar(30))

Unique primary index(party id);

Create set table test


Party id integer,

Party name varchar(30))

Primary index(Party id);


Create set table test1


Party id integer,

Party name varchar(30),

Party code integer)

Primary index(Party id) partition by case- N

Party code=10, party code=20, no case, unknown);


Works only with NUPI values

Learn more about Teradata Interview Questions in this blog post.


Based on the range of data as part of partitions

We go for it

Create set table test1


Party id integer

Party name varchar(30),

Party code integer,

Jdate date) Unique primary index

(Party ID, J date)partition by range – N(J date between

‘2010-01-01’And’2010-10-01’ Each interval’1’ month)


Works only with UPI Values



Each level must have 2 partitions

Max 15 Levels supports

Total partitions 65,535

NOPI(only in Teradata 13)


We can take as stage tables to load data from fast load(or) TPUMP Array insert

Create multiset table let(party id integer, party name, varchar(30))

No Primary index:


For Indepth knowledge on Teradata click on: