Comparing Autonumbering Methods in Different Relational Database Management Systems

Date: May 1, 2002

Article is provided courtesy of Addison Wesley.

Most relational database management systems (RDBMSs) provide a way to create unique sequential numbers. These numbers are handy when you want each new customer or part you enter to automatically have a different numeric identifier on INSERT. This article examines some of the methods used.

Autonumbering

Most relational database management systems (RDBMSs) provide a way to create unique sequential numbers. These are handy when you want each new customer or part you enter to have a different numeric identifier, automatically, on INSERT. This article, derived from Practical SQL: The Sequel (Addison-Wesley, 2000) examines some of the methods used.

Comparing Systems

Although vendors provide the desired numbering capability, the mechanisms they provide vary a great deal. In four systems examined, there are four quite different approaches:

Column default
Column property
Database object
Special datatype

Because sequential numbers are so useful, the difference in how they are created presents some special problems for porting from one system to another. Will you need to change the CREATE TABLE statements? Add objects? Rewrite your inserts?

The following sections give examples of how four vendors handle sequential numbers: Sybase's small footprint Adaptive Server Anywhere (ASA), Microsoft SQL Server, Oracle, and Informix. You'll find a summary in Table 1.

Table 1: Sequential Numbering

Topic	Sybase Adaptive Server Anywhere	Microsoft SQL Server	Oracle	Informix
Creating sequential numbers Creating sequential numbers	DEFAULT AUTOINCREMENT in CREATE TABLE or IDENTITY column property in CREATE TABLE	IDENTITY column property in CREATE TABLE	CREATE SEQUENCE seq	SERIAL datatype in CREATE TABLE
Inserting the next value	INSERT non-default values only INSERT non-property values	INSERT non-property values	INSERT seq.nextval	INSERT 0 for SERIAL column to get next number

Topic

Sybase Adaptive Server Anywhere

Microsoft SQL Server

Oracle

Informix

Creating sequential numbers Creating sequential numbers

DEFAULT AUTOINCREMENT in CREATE TABLE

IDENTITY column property in CREATE TABLE

CREATE SEQUENCE seq

SERIAL datatype in CREATE TABLE

Inserting the next value

INSERT non-default values only

INSERT non-property values

INSERT seq.nextval

INSERT 0 for SERIAL column to get next number

ASA: Default

On Sybase's ASA, you define autonumbering as a default in the CREATE TABLE statement. The column must be integer or exact numeric datatype, and performance is best if the column is defined as PRIMARY KEY or is the first column of an index. This allows the system to find the high value without searching the entire table.

Adaptive Server Anywhere

create table testseq
(num int not null default autoincrement,
name varchar(10) not null)

[table created]

When you INSERT rows, you give a value for the name column, but none for num:

Adaptive Server Anywhere

insert into testseq (name)
values ('Amir')

insert into testseq (name)
values ('Marge')

insert into testseq (name)
values ('Tri')

[3 rows]

A SELECT shows sequential numbers in the num column.

Adaptive Server Anywhere

select *
from testseq
    num name   
=========== ==========
     1 Amir
     2 Marge
     3 Tri

[3 rows]

Transact-SQL: Column Property

In Transact-SQL, the autonumbering feature is a column property rather than a default. Microsoft SQL Server and Sybase ASE have the same approach, but show some differences.

In Microsoft SQL Server, IDENTITY can take start and increment values (here it is set to start at 1 and increment by 1).

Microsoft SQL Server

create table testseq
(num int not null identity (1,1),
name varchar(10) not null)

The command(s) completed successfully.

You INSERT just as in ASA, and when you look at the rows, you see the automatically inserted numbers.

MS SQL Server

insert into testseq (name)
values ('Amir')
....

select *
from testseq

num     name    
----------- ---------- 
1      Amir
2      Marge
3      Tri

(3 row(s) affected)

Oracle: CREATE SEQUENCE

Oracle requires you to create a sequence as a separate object and then use the sequence object in the INSERT. The sequence has no role in the CREATE TABLE statement.

Oracle

SQL> create sequence newseq;

Sequence created.

SQL> create table testseq
 2 (num number not null,
 3 name varchar2(10) not null);

Table created.

The sequence you create can have any name, but you must use nextval with it in the INSERT to generate unique values.

Oracle

SQL> insert into testseq
 2 values ( newseq.nextval, 'Amir');
1 row created.

Use the same code for two more inserts, changing the value in the name column so that you get rows for Marge and Tri. When you check the table, you'll find auto numbers in place.

Oracle

SQL> select *
 2 from testseq;
   NUM NAME
--------- ----------
    1 Amir
    2 Marge
    3 Tri

3 rows selected.

Informix: SERIAL Datatype

Informix handles sequential numbers in the CREATE TABLE statement with a special SERIAL datatype. Specifying 0 for the SERIAL column during INSERT makes Informix assign the next sequential number.

Informix

create table testseq
(num serial not null,
name varchar(10) not null)

Table created.
insert into testseq
values ( 0, 'Amir')
insert into testseq
values ( 0, 'Marge')
insert into testseq
values ( 0, 'Tri')

select *
from testseq

    num name    
     1 Amir   
     2 Marge   
     3 Tri 
[3 rows]

Associated Issues

With this much variation in autonumbering, be prepared to do some work when you move from system to system. Check your documentation for information in these areas:

Specifying datatypes and other column properties (null/not null, for example)
Setting the start number and increment value
Relating the sequence element to table constraints and indexes
Locating the current highest number
Creating ranges or maximums
Dealing with DELETEs and UPDATEs (reuse the number or leave a gap?)
Overriding autonumbering

Most SQL engines have a rich set of tools to manage the sequential numbers.