Oracle: difference between max(id)+1 and sequence.nextval

I am using Oracle

What is difference when we create `ID` using `max(id)+1` and using `sequance.nexval`,where to use and when?

Like:

insert into student (id,name) values (select max(id)+1 from student, 'abc');


and

insert into student (id,name) values (SQ_STUDENT.nextval, 'abc');

`SQ_STUDENT.nextval` sometime gives error that duplicate record...

please help me on this doubt

Using a sequence works if you intend to have multiple users. Using a `max` does not.

If you do a `max(id) + 1` and you allow multiple users, then multiple sessions that are both operating at the same time will regularly see the same `max` and, thus, will generate the same new key. Assuming you've configured your constraints correctly, that will generate an error that you'll have to handle. You'll handle it by retrying the `INSERT` which may fail again and again if other sessions block you before your session retries but that's a lot of extra code for every `INSERT` operation.

It will also serialize your code. If I insert a new row in my session and go off to lunch before I remember to commit (or my client application crashes before I can commit), every other user will be prevented from inserting a new row until I get back and commit or the DBA kills my session, forcing a reboot.

With the `select max(id) + 1` approach, two sessions inserting simultaneously will see the same current max ID from the table, and both insert the same new ID value. The only way to use this safely is to lock the table before starting the transaction, which is painful and serialises the transactions. (And as Stijn points out, values can be reused if the highest record is deleted). Basically, never use this approach. (There may very occasionally be a compelling reason to do so, but I'm not sure I've ever seen one).

The [sequence guarantees that the two sessions will get different values](

[To see links please register here]

), and no serialisation is needed. It will perform better and be safer, easier to code and easier to maintain.

The only way you can get duplicate errors using the sequence is if records already exist in the table with IDs above the sequence value, or if something is still inserting records without using the sequence. So if you had an existing table with manually entered IDs, say 1 to 10, and you created a sequence with a default start-with value of 1, the first insert using the sequence would try to insert an ID of 1 - which already exists. After trying that 10 times the sequence would give you 11, which would work. If you then used the max-ID approach to do the next insert that would use 12, but the sequence would still be on 11 and would also give you 12 next time you called `nextval`.

The sequence and table are not related. The sequence is not automatically updated if a manually-generated ID value is inserted into the table, so the two approaches don't mix. (Among other things, the same sequence can be used to generate IDs for multiple tables, as mentioned in the docs).

If you're changing from a manual approach to a sequence approach, you need to make sure the sequence is created with a start-with value that is higher than all existing IDs in the table, and that everything that does an insert uses the sequence only in the future.

To add to the other answers, a couple of issues.

Your max(id)+1 syntax will also fail if there are no rows in the table already, so use:

Coalesce(Max(id),0) + 1

There's nothing wrong with this technique if you only have a single process that inserts into the table, as might be the case with a data warehouse load, and if max(id) is fast (which it probably is).

It also avoids the need for code to synchronise values between tables and sequences if you are moving restoring data to a test system, for example.

You can extend this method to multirow insert by using:

Coalesce(max(id),0) + rownum

I expect that might serialise a parallel insert, though.

Some techniques don't work well with these methods. They rely of course on being able to issue the select statement, so SQL*Loader might be ruled out. However SQL*Loader has support for this technique in general through the SEQUENCE parameter of the column specification:

[To see links please register here]

Assuming MAX(ID) is actually fast enough, wouldn't it be possible to:

> - First get MAX(ID)+1
> - Then get NEXTVAL
> - Compare those two and increase sequence in case NEXTVAL is smaller then MAX(ID)+1
> - Use NEXTVAL in INSERT statement

In that case I would have a fully stable procedure and manual inserts would also be allowed without worrying about updating the sequence