Autonumerics: get the latest embedded in Oracle, MySQL, and SQL Server

Hi friends!!! In today’s post we are going to work with Autonumerics in Oracle, MySQL and SQL Server . We will see how to obtain the last value of the fields IDENTITY (SQL SERVER), AUTO_INCREMENT (MySQL) and IDENTITY (ORACLE from version 12c. Sequence in previous versions).

Autonumerics: how to get the last inserted in Oracle, MySQL and SQL Server | gpsos.es

If you have in mind to make a select MAX (…) from field to consult … DO NOT DO IT! It’s not a good idea. The problem is that if you try It will work fine on your test rig, but it won’t really work for you.

Why is it unreliable to get auto-numbers with Select MAX?

The reason is that in environments with many simultaneous users, between when you enter your order in the table and make the query to see the maximum ID, another user may have entered another order in the same table that will already have a higher ID. If you simply find out the maximum you run the risk (almost certainly) that your order lines and your customer will be associated with an order that does not correspond to them. Do you see the problem?

Let’s see how to obtain the autonumeric values in Oracle, MySQL and SQL Server

SQL SERVER:

SELECT SCOPE_IDENTITY ()

Returns the last identity value inserted in a identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two instructions are in the same stored procedure, function, or batch, are in the same scope.

MYSQL:

SELECT LAST_INSERT_ID ()

In the case of MySQL this statement returns the last auto-numeric that has been generated in the same connection that we are using currently. As long as we don’t share connections (something unlikely in any case) we would have no problem getting it with confidence.

ORACLE:

In the case of Oracle, it is a bit more complex. To get started, and incredibly, Oracle did not have auto-incremental fields until release of its 12c version in June 2013. Before that you had to manage them using Sequences and triggers to achieve the same.

 CREATE TABLE Orders (
 OrderID INT PRIMARY KEY,
  ....
 ); 

We also have to create a sequence of the order id

CREATE SEQUENCE OrderSequence
MINVALUE 1 MAXVALUE 1000 INCREMENT BY 1;

And a trigger that triggers the sequence.

CREATE OR REPLACE TRIGGER orders_bit
 BEFORE INSERT ON Orders
 FOR EACH ROW
 BEGIN
 SELECT OrderSequence.NEXTVAL
 INTO: new. OrderID
 FROM dual;
 END;
 As a step 

As a final step, we had to make a returning to insert

 INSERT INTO Orders (CustomerID, EmployeeID, OrderDate ....)
 VALUES (1, 1 ...)
 RETURNING OrderID INTO: last_insert_id 

In version 12c they have improved it and the creation sentence of the incremental car is:

 CREATE TABLE Orders
 (OrderID NUMBER GENERATED ALWAYS AS IDENTITY, ...); 

Now, yes, to obtain the value we must do exactly the same as before in the insert statement, that is, use RETURNING INTO to get the value, as we just saw. More information in https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/creating-tables-identity-column.html

We hope you have been useful friends! See you next time, if you need help with this or another database issue, do not hesitate to contact with us.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn