SQL Server Auto-Increment – Creating 7 Digit Codes for New Insertions

In Informix, a SERIAL column is a 4-byte signed integer with some auto-increment properties.

If you insert the value 0 into a SERIAL column, or fail to provide a value for the serial column, the next number in ascending order will automatically be assigned for this row. In a programming language such as ESQL/C, you can retrieve the value inserted from the SQLCA record. There are also, I think, functions to retrieve the last serial value inserted. If you insert a value into a SERIAL column, that is the value that will be use. Clearly, if there is a unique constraint on the column (there isn't one automatically, though the DB-Access Schema Editor will add one for you) and you insert a duplicate record, then the insertion will fail. If there is no unique constraint, it will succeed. If the newly inserted value, N, is larger than any previously inserted, then the internal counter will be incremented so that the next row inserted with 0 will be assigned N+1.

The second part of the question asks:

• What will happen when the SERIAL value reaches the maximum?

  • The answer is that it wraps around to 1 again. The new values will be inserted, and as long as the new value does not collide with a value already in the table, all will be well.
  • Note that negative and zero values are all skipped. You can insert negative values explicitly. AFAIK, you cannot insert a value of zero into a SERIAL column¹.

This leads to an answer for the first part of the question:

• Is there any method to initialize the serial again to zero?

  • There is. You insert 2³¹-1 into the serial column. The next row you insert will be assigned the value 1 (not zero). It will keep going from there. Note, however, that if there are many values from the first cycle still in the table, this will cause problems; insertions will fail because the value is already in use.

  • Once upon a very long time ago, in versions that are, I believe, safely out of service (we're talking early or mid 1990s here), you had to insert 2³¹-2 and then 2 rows with zeroes. If you inserted 2³¹-1 directly, the system got confused and jammed itself.

¹ You can get a row where there is a zero in the SERIAL column, but only through a back-door cheat. You have to have the column as a plain INTEGER column when you insert the row; then you alter the table so the column is a SERIAL column. So, as I said, you can't insert a row with zero in the SERIAL column, but you can find a row with zero in the SERIAL column if someone is devious enough.

Demonstration

SQL commands prefixed with a + and a blank; output from the DBMS without that prefix. Note that this table was created without a unique constraint or primary key on the serial column; this is not what I'd normally do.

+ begin work;
+ create table serial_x(s serial(100) not null);
+ insert into serial_x(s) values(0);
+ insert into serial_x(s) values(0);
+ insert into serial_x(s) values(0);
+ select * from serial_x;
100
101
102
+ delete from serial_x;
+ alter table serial_x modify(s serial(1));
+ insert into serial_x(s) values(0);
+ select * from serial_x;
103
+ alter table serial_x modify(s serial(1000));
+ insert into serial_x(s) values(0);
+ select * from serial_x;
103
1000
+ insert into serial_x values(2147483647);
+ select * from serial_x;
103
1000
2147483647
+ insert into serial_x values(-1);
+ insert into serial_x values(-1);
+ insert into serial_x values(0);
+ select * from serial_x;
103
1000
2147483647
-1
-1
1
+ rollback;

I formally checked that ALTER ... SERIAL(0) produces the same result as ALTER ... SERIAL(1).

I personally don't see a problem with having both a ID and a UID, I use this technique quite a lot actually. However, I still use the primary key to perform the JOIN on as this performs better - the UID is used mainly in the WHERE clause when searching for records.

In your example, the biggest point I would make is not to use a VARCHAR(36) for your UID - I would use BINARY(16) as this fits a UUID perfectly. The CHAR type is 1 byte and so you are using an extra 20 bytes of storage to persist your UUID. Also, joining on this column will be slower than a join on the BINARY(16) equivalent.