better way to create/assign unique sequence number to all records in teradata

General

better way to create/assign unique sequence number to all records in teradata

Hello Team,

 

I am new to terada and this is my post in this community.

 

I got one business requirment to assign unique sequence number to all records in a Teara data table.

 

currently table have 8 Millian  records and two columns are defined as Primary index fields

 

I have been browsing through to get answer to  this question and read through IDENTITY and CSUM are two ways for sequence,but Identity is out of scope here since it generate uniqueu random numbers.

and to begin with CSUM(1,1) - i read all posts says it will spool out and i personnaly expereinced it

 

the last option i have seen is row_number ()  over Partion by 0 order by C1,C2 

here in C1,C2 i used primary index fields that was defined currently on table..but that didnt give me solution what i am looking for

 

irrespective of data i would need to assign a unique sequence number to each row on the table,could anyone help me on this.

 

any help greatly appreciated . Thanks 

 

 

 

1 REPLY 1
Supporter

Re: better way to create/assign unique sequence number to all records in teradata

Dear Lakshmaiah,

IDENTITY does not assign random numbers. Each AMP has it's own range of sequential numbers to use. The result is a non-contigues assignment of numbers.
You say this is out of scope, so your requirement must be 'sequentially assigned ID, maintaining arrival order'. If so, then you may want to look at using TIMESTAMP(6), or a derivitive of it.

 

Use of CSUM or ROW_NUMBER or any other STAT function, on your target, will become costly over time.
You could use ROW_NUMBER over only the incoming data.
Then and add a stored LAST_BATCH_NUMBER you keep after each cycle, or add a simple MAX on your target.

 

Cheers,

Teradata Frank, Certified Master