Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Auto Incrementing Column
- Thread starter jmcmaster
- Start date
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
or
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.
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
or
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.
- Thread starter
- #4
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.
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.
Guest_imported
New member
- Jan 1, 1970
- 0
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.
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.
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.
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
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
- Status