Comparing Autonumbering Methods in Different Relational Database Management Systems
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 |