Home > Articles > Data > Oracle

Comparing Autonumbering Methods in Different Relational Database Management Systems

  • Print
  • + Share This
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.
From the author of

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


  • + Share This
  • 🔖 Save To Your Account