Auto Incrementing Column Auto Incrementing Column jmcmaster (MIS) (OP) 11 Jun 01 13:50 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?Thanks,JLM RE: Auto Incrementing Column tdatgod (Programmer) 21 Jul 01 05:30 Hi, 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 tdatgod (Programmer) 1 Aug 01 05:08 Hi, 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 orAre 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 jmcmaster (MIS) (OP) 1 Aug 01 08:24 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 tdatgod (Programmer) 1 Aug 01 12:46 Thanks for the information. I will let our development team know. RE: Auto Incrementing Column Guest (visitor) 1 Aug 01 15:59 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 tdatgod (Programmer) 2 Aug 01 00:20 Hi, 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 DugzDMan (Programmer) 1 Aug 03 14:13 Does anyone know if this has been added yet? (Using V2R5) RE: Auto Incrementing Column dnoeth (Instructor) 2 Aug 03 05:30 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.Dieter RE: Auto Incrementing Column BillDHS (Programmer) 2 Aug 03 21:38 I would appreciate the visitor no using my alias. Try being a little creative.