Auto Number or Auto Increment

Auto Number or Auto Increment

Post by Vinay Bansa » Sat, 17 Jul 1999 04:00:00

How can I increment a number in SQL automatically?

Auto Number or Auto Increment

Post by DPSatHy » Sat, 17 Jul 1999 04:00:00

Not sure if this is what you are looking for but you can use an INDENTITY

Quote:> How can I increment a number in SQL automatically?


Auto Number or Auto Increment

Post by Russell Field » Sat, 17 Jul 1999 04:00:00

If the identity does not answer your need, then you have to do it yourself.

This is a complicated question in a multi-user system.  Basically, you will
need to create a table that maintains the next value and always update it in
a locking transaction.

Next question:  How to avoid gaps in the numbers?  Nearly impossible.

Russell Fields, SQL Server MVP


1. Auto Depressing Auto Increment Workarounds

I was shocked to know that DB2 lacks AUTO INCREMENT capability and it is
only going to have it
in Version 7.x as IDENTITY columns. Coming from the world of mySQL, it was
quite depressing to look for workarounds
for a feature that should be given for any database like DB2.

My frustration can be summed up as follows:

1. Any time I try a workaround for the auto increment, an exclusive table
lock is needed
   to guarantee protection from INSERT collisions between multiple instances
of my application running on multiple nodes.

2. Exclusive mode table locks are expensive even if the table contains a
single row.

Below are the descriptions of a few attempts I made in solving this problem.
Any speed-up suggestions or pointers will be greatly appreciated.

Attempt #1 Using coalesce() with the column MAX() function

a. Had my application use the coalesce() with max(column) + 1 to update an
ID field.

b. Worked fine until two instances of the application ran on two nodes and
got many insert failures.

c. Added exclusive table level lock in the app.

d. Worked but 100K inserts took 18 minutes! Without the table level lock in
a single instance of
   the app took less then 10 minutes!

Very depressing since I have millions of inserts to process! So moved to
attempt #2...

Attempt #2: Using a Trigger to Update a Special Counter Table

I did the following

a. Create a single column table called AutoCNT with a column called LASTCNT.

b. Inserted 1 manually to set LASTCNT to 1.

c. Created a trigger to update the LASTCNT after each INSERT processing for
the table with ID field

d. Had my application grab the LASTCNT as ID in a sub-select for the INSERT.

e. Ran the app. The LASTCNT got incremented by the trigger as expected.

f. Ran multiple instances of the application from multiple nodes. BIG

g. Got 10-15 INSERT failures per 1000 inserts!

h. Fixed the problem by changing application code to perform:

 1. Disabled auto commit

 2. Locked the counter table in EXCLUSIVE MODE

 3. Performed INSERT into the table with ID field.

 4. Enabled commit.

i. Worked! No collisions in insert operations. Alas very slow!

j. Benchmarked application to simply INSERT IDs. Got about 80-90 inserts per
   No network latency mode of the benchmark showed the upper limit of the
above range.
   Network latency caused the lower limit to be the average.

Note: DB2 running on Red Hat Linux with multi GB of RAM and multi Xeon



2. Performance degredation when explicitly calling out column names

3. auto-number increment field

4. I'm Only Here For A Quickie

5. Auto increment of values containing numbers and characters in SQL server

6. US-DC-Wanted: Client/Server Development Professionals

7. How to auto increment number in Access Table through VB form


9. ABF) Resetting auto-increment numbers in tables

10. Finding the auto-increment number

11. FM5: Multiple auto-incrementing numbers

12. Auto-Incrementing a Serial Number Problem

13. Auto-update a number field w/ Auto-Enter Calculation