Usage of Sequence Generator in Teradata

Database
Enthusiast

Usage of Sequence Generator in Teradata

Hi TD Gurus,

 

I have one small query regarding TD Sequence Generator. Using below syntax to create table.

 

CREATE SET TABLE database.tablename,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
     col1 BIGINT TITLE 'sno' NOT NULL GENERATED ALWAYS AS IDENTITY
           (START WITH 1
            INCREMENT BY 1
          --  MINVALUE -999999999999999999
          --  MAXVALUE 999999999999999999
            NO CYCLE),

col2 varchar(20)

)UNIQUE PRIMARY INDEX ( col1 );

 

When i am executing it automatically values are populating in the table starting from 1. If suppose there are two rows in the file then it is behaving in below pattern.

1st Run :

1  abc 

2  def

 

2nd run

1  abc 

2  def

 10001  cdf

10002  dfg

 

3rd Run:

1  abc 

2  def

 10001  cdf

10002  dfg

20001  ghj

20002  klm

 

4th Run:

1  abc 

2  def

 10001  cdf

10002  dfg

20001  ghj

20002  klm

30001 fdfd

30002 dfdfd

 

and so on....

 

So my query is ideally it should be in the sequence order. Can someone pls help me here. Is it something related to configuration issue with TD or BTEQ ? Please advise. Many Thanks.


Accepted Solutions
Highlighted
Teradata Employee

Re: Usage of Sequence Generator in Teradata

These are Identity columns, not Sequencing columns.  Each of the parallel units of operation ("vprocs") in the DBMS has its own range of values to assign to identity columns.  For details, see the SQL DDL Detailed Topics / Create Table Options / Column Definition Clause / Process for Generating I....  Even within a vproc, ID columns are not guaranteed to be sequential.

You can generate sequence numbers using the Row_Number() Ordered Analytical Function or with logic in your batch data loading program.

1 ACCEPTED SOLUTION
1 REPLY
Highlighted
Teradata Employee

Re: Usage of Sequence Generator in Teradata

These are Identity columns, not Sequencing columns.  Each of the parallel units of operation ("vprocs") in the DBMS has its own range of values to assign to identity columns.  For details, see the SQL DDL Detailed Topics / Create Table Options / Column Definition Clause / Process for Generating I....  Even within a vproc, ID columns are not guaranteed to be sequential.

You can generate sequence numbers using the Row_Number() Ordered Analytical Function or with logic in your batch data loading program.