Support for generating unique sequence numbers

Database
Enthusiast

Support for generating unique sequence numbers

Does Teradata support a database object for generating unique sequence numbers (like Oracle's Create Sequence), if not, any ideas?
Thnx
4 REPLIES
Teradata Employee

Re: Support for generating unique sequence numbers

Teradata does not have a SEQUENCE object type. Best alternative will depend on what you are doing with the values. For example, do they have to be strictly increasing with no gaps, or just unique? Do they have to be unique for all time or only for the duration of some process (e.g. ETL/ELT)?
Enthusiast

Re: Support for generating unique sequence numbers

Teradata provides the standard (SQL:2003) IDENTITY attribute for columns.

An identity column has, as a default value, an automatically generated unique number. This can usually serve as a satisfactory alternative to the Oracle-style sequence generator, and it is far simpler to use.
Enthusiast

Re: Support for generating unique sequence numbers

I think IDENTITY columns have their own limitations and are not synonymous as sequences. I would recommend you to use ROW_NUMBER identified to generate unique numbers. You could look up for MAX of existing number and start generating the sequence.

Vinay
Enthusiast

Re: Support for generating unique sequence numbers

Hi Vijay,

Can you explain more about how to use ROW_NUMBER?

Regards,
JMM