Why doesn't Teradata identity column increment by 1?

Question

I have the following table

CREATE SET TABLE myTab,FALLBACK,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT,DEFAULT MERGEBLOCKRATIO
     (
      my_id BIGINT GENERATED ALWAYS AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 922337203685477580 
            NO CYCLE),
      created_by VARCHAR(200) )
UNIQUE PRIMARY INDEX ( my_id );  

When I started insetring into this table (INSERT INTO myTab (created_by) VALUES ('test'), here are the values for my_id that got auto generated

my_id
1
100,001
100,002
200,001
300,001
400,001
500,001
500,002
600,001

I was looking to get 1,2,3 . . . incremented by 1. I read the teradata explanation but still don't understand. Why does it increment by a random number ? This way I will reach the MAXVALUE 922337203685477580 pretty soon. What will happen then ?

Answer 1

I believe the explanation you are seeking can be found in the SQL Data Definition Language - Detailed Topics under Chapter 5: Create Table, Process for Generating Identity Column Numbers. Identity columns are not intended to provide an accurate, sequential order for which the rows are loaded into a table.

If you want a sequential identifier, you will have to maintain that yourself in your ETL with a combination of the max value in the table plus the ROW_NUMBER() assigned to the data set you are loading into the table. Otherwise, you may want to reconsider whether a sequence id/row num is actually required in your implementation.