Whats the standard way of getting the last insert id?

Question

What's the sql standard to get the last inserted id? If there is such a thing.

mysql: LAST_INSERT_ID()
postgresql: ... RETURNING f_id
mssql: SCOPE_IDENTITY()
... more examples here ...

I mean, all databases have different implementations for that, there isn't a standard for such a common task?

Answer 1

See this answer Retrieve inserted row ID in SQL

In short, there is no cross database way to do this, except MAX(ID) - but that is not a guaranteed result and has many many pitfalls, e.g.

• other inserts can come between last insert and max query
• cannot be used with high transaction tables (max will issue a read lock, rdbms-specific methods do not read from any table)

The ANSI standard that relates to identity/autonumber/auto_increment/sequences first appeared in SQL:2003 awaiting implementation by all major RDBMS. It will most likely resemble Oracle/PostgreSQL sequences.

The SQL:2003 standard makes minor modifications to all parts of SQL:1999 (also known as SQL3), and officially introduces a few new features such as:

- the sequence generator, which allows standardized sequences

Another change in SQL:2003 is the OUTPUT USING CLAUSE but there is very little information about it. Sybase and SQL Server have done different things with it, so it is unclear as yet how it will pan out. SQL Server implements it as

INSERT INTO TBL(..)
OUTPUT inserted.identity_col
INTO @sometablevar
VALUES(..)

Answer 2

Oracle and PostgreSQL support the RETURNING clause, and use an object called a sequence to provide automatic sequential numbering. The next version of SQL Server, denali, is set to support sequences, but I haven't seen word if Denali will support the RETURNING clause. Another means of getting the current sequence value is:

Oracle: sequence_name.CURRVAL 
PostgreSQL: CURRVAL('sequence_name')

DB2 supports sequences, and the RETURNING INTO clause.

SELECT MAX(auto_increment_column) ... is not a recommended practice because it's not reliable. In Oracle, readers (SELECT) aren't blocked by writers (INSERT/UPDATE) so the value can't be guaranteed correct.

Conclusion

I wasn't aware that the ANSI SQL:2003 standard includes using sequences for autonumbering, but at this time there's no consistent means implemented for retrieving the value.