Re: Cross database queries - a survey of sorts

"runstein" <replytogroup_at_sickofspam.com> wrote in message news:<uov7r3b0qof4db_at_corp.supernews.com>...
> Also, SQL Server has what I assume is a proprietary method for inserting a
> record in a table with an auto-incrementing (identity) field, and then
> returning the newly created number. How do other database systems handle
> this?
>

This is a partial repeat of a posting I made for a similar question on comp.databases.oracle last month, re "sequence portability" in SQL.

There is some division between DBMSs who support "identity" columns and DBMSs who support sequences, though it's possible to do both (IBM does). Supporters of "identity" include MySQL (with the AUTO_INCREMENT property), Informix (with the SERIAL datatype), and Microsoft+Sybase (with the IDENTITY property).

Supporters of CREATE SEQUENCE, besides Oracle, include IBM (i.e. DB2) and PostgreSQL.

PostgreSQL syntax, slightly simplified, is: CREATE [TEMPORARY | TEMP] SEQUENCE name [INCREMENT n] [MINVALUE n] [MAXVALUE n] [START n]
[CACHE cache ] [CYCLE ]

DB2 syntax, slightly simplified, is:
CREATE SEQUENCE name [AS datatype] [START WITH n] [INCREMENT BY n] [MINVALUE n] [MAXVALUE n] [CTCLE] [CACHE cache] [ORDER]

Proposed ANSI/ISO syntax, slightly simplified, is: CREATE SEQUENCE name [AS datatype] [START WITH n] [INCREMENT BY n] [MINVALUE n] [MAXVALUE n] [CYCLE]
... where the clauses can appear in any order. ANSI/ISO also allows for IDENTITY clauses in column definitions; it defines IDENTITY as just a form of a sequence that's bound to a column.

I am only using the CREATE SEQUENCE statement as an example that other DBMSs have similar concepts and syntaxes. To give now an example of where concepts might differ: there is some question about where the sequence's "next value" is stored. On one extreme, PostgreSQL stores as a table, that is, it's part of the database. On the other extreme, according to a member of the ANSI/ISO committee, it can be a session attribute, that is, it's part of the database instance. The decision could affect: whether a change causes a lock, whether commit and rollback make a change permanent, and whether shutting down could cause a reset to the START WITH value.

On informit.com, which is a free-but-you-get-on-a-mailing-list site, Judith S. Bowman's article "Comparing Autonumbering Methods in Different Relationalatabase Management Systems" has some examples that show how different DBMSs get the next number after a sequence is created. We also discuss the matter in our book.

Peter Gulutzan
Latest article: "Transaction Logs" on http://dbazine.com Co-Author of SQL Performance Tuning
(http://ourworld.compuserve.com/homepages/ocelotsql/tuning.htm) Received on Thu Sep 26 2002 - 16:26:09 CEST