Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Auto Incrementing Column

Auto Incrementing Column

Auto Incrementing Column

Is there a way to create a column in a table that will be an autoincremented number when a new record is added by the user?  Like an Autonumber field?


RE: Auto Incrementing Column

   The Answer is No.  We have an outstanding Partners enhancement request to implement this feature.  It has yet to be scheduled for implementation.

RE: Auto Incrementing Column

   I need to ask a question about your desire for this auto Incrementing number.

Are you trying to use this to identify the ORDER by which records were added  


Are you just looking for a way to have a UNIQ IDENTIFIER on every row generated and maintained by the system so you can have a UNIQUE PRIME INDEX to allow you to insert tons of data with no real PRIME INDEX and have it distributed evenly.

Since we don't have not yet scheduled the implementation of this feature the interpretation of the request is still under investigation.

Our current thinking wasn't to use an incrementing number because on a paralell system like teradata Incrementing that SINGLE CELL would become a bottle neck of contention and performance would be non existent.

As Codd and Date, the fathers of Relational model point out,  there is no order to a relation or the columns of that relation internally.   Once Projected to a real world SET then you can order the SET.

Therefore to say this ROW was inserted before that ROW violates the relational model.


RE: Auto Incrementing Column

We have an application that is used to enter quotes for customers.  We would like the database to assign a new unique number to each quote automatically.  We are currently using MSACCESS for these tables which provides an auto-incrementing field in a table so we use this auto-incrementing field for our unqiue quote number.  

We had considered writing code that would check the teradata table to determine the highest quote number entered and generate a new quote number by adding one to the highest number in the table.  However, this seems really messy.  It would be a lot easier if there was a auto-incrementing field in Teradata like in SQL and MSACCESS.

RE: Auto Incrementing Column

Thanks for the information.  I will let our development team know.

RE: Auto Incrementing Column

Would it work to add a timestamp(6) field and fill it with the current time  as a default when the row is created? This would have an order to it and may work as a proxie to a system generated ID.  This auto-increment question has been posed multiple times in the past and NCR should certainly review the issue.   

RE: Auto Incrementing Column

   Yes the Timestamp(6) works as long as you don't load data too quickly.  However on parallel nodes using something like TPUMP through multiple sessions it would be possible to get the same time stamp down to the micro second, not likely but possible.

The Auto incrementing number doesn't work because on a parallel box across 60 or 120 nodes, the single cell will become a very HOT commodity.  

Just imaging the LAN traffic to update this cell during a TPUMP job.

Then you have WRAP Issues if it is an integer what do you do at 4 GIG.   I still remember the time sel count(*) blew up at a customer because they had more than 4 billion rows in a single table.

If teradata had a native 64 bit Integer we could use that and wouldn't not have to worry about wrap for a while but eventually it would.

I guess you could make it a Decimal but some whole numbers have no real IEEE Decimal representation.

The current thinking is something based upon the time stamp and the Clock ticks and maybe the session id which one would could be combined to form a UNIQUE IDENTIFIER.

But again there is no gaurentee that the identifier would be INCREMENTING just UNIQUE across all nodes.

Like I said we are planning this for a future release we just have to come up with something that we can gaurentee will be UNIQUE all the time no matter how fast or slow you add your data and that is performant since the bottom line is if it doesn't perform well customers won't use it.

RE: Auto Incrementing Column

Does anyone know if this has been added yet?  (Using V2R5)

RE: Auto Incrementing Column

It has been added to V2R5, but different from tdatgod's description (hey, he's probably not omniscient ;-)

They implemented sequences (only on column level, no CREATE SEQUENCE) in SQL:1999 style with a syntax similar to DB2 and Oracle, e.g.:
create table foo(
  seq integer generated by default as identity (start with 1 increment by 1)

- data types may be integers or decimal up to dec(18,0)
- it's guaranteed to be unique if it's GENERATED ALWAYS and there's no CYCLE option
- if you insert rows in a batch the numbering usually doesn't reflect the order of rows in your input file
- there may be gaps
- FastLoad/MultiLoad are not yet supported (V2R5.1)

Check the SQL Manual Vol. 4, Chapter 1, CREATE TABLE (Column Definition Clause) for details.


RE: Auto Incrementing Column

I would appreciate the visitor no using my alias.  Try being a little creative.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close