Identity column question - increment behaviour

Database

Identity column question - increment behaviour

I'm creating a table with the following column..

COLUMN1 BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 300000000000 INCREMENT BY 1 MINVALUE 300000000000 MAXVALUE 399999999999 NO CYCLE)

..which i'm then loading into, sometimes with an real value to be retained, otherwise DEFAULT(COLUMN1) to create an ID starting at 300000000000.

What I wasn't expecting is that after loading, the values don't strictly increment by 1 - for example I have 30000000000, 300000000001, 300000000002 then 300000100000, 300000100001, 300000100002 then 300000200000, 300000200001, 300000200002..

I don't think it's a problem as all the values retain uniqueness, I'm just not sure what the cause of this behaviour is - perhaps use of BIGINT and a large starting number? Or perhaps due to the 'GENERATED BY DEFAULT' syntax as I don't think this was happening when I used 'ALWAYS' syntax instead..

Thanks

6 REPLIES
Enthusiast

Re: Identity column question - increment behaviour

That's the limitation of Identity column.after a sequence it will produce random numbers,for getting a sequence better go with a row_number function or a window function
Enthusiast

Re: Identity column question - increment behaviour

Each AMP is assigned a pool of numbers in the range you specify, therefore depending on which AMP the row is written to, determines which numbers are actually used. 

Teradata Employee

Re: Identity column question - increment behaviour

StevenSchmid is right. Teradata works as a parallel system. To get strictly increment by 1 the work of the diferent AMPs should be sequential (not parallel), this would go against Teradata philosofy so each AMP is assigned a range of numbers to keep parallel working.

Re: Identity column question - increment behaviour

Makes sense - thanks for clearing that up.. It's funny as I'm pretty sure when using ALWAYS GENERATED, it was consistently incrementing by 1 which would suggest the numbers aren't spread across AMPs and thus is a less efficient design using that methodology?

Teradata Employee

Re: Identity column question - increment behaviour

ALWAYS GENERATED-> If you insert a new row you get a new value for that column. It does not matter if  you specify that column or no. 

GENERATED BY DEFAULT-> If you want you can insert your own value for that column so Teradata only generate a new value for that column when you don't specify a new value for that column.

Teradata Employee

Re: Identity column question - increment behaviour

For single-row INSERTs, the PE obtains the next identity column value. For load or INSERT/SELECT, which potentially can involve many rows, the AMPs each obtain a "batch" of numbers as described above.