I am working on re-factoring a database. There is a process that inserts a row in one of two tables, and the id needs to be unique across the two tables.

Currently the application layer iterates through a predetermined array of keys.

I am considering a Sequence to generate the next id, as apposed to a SELECT MAX(id) + 1.

Are there any performance gains to be realized from using a Sequence?

  • 3
    Sequences are cached, there'd be no need to go out and grab that max id any longer, which would mean fewer queries, and improvements in throughput (as youd no longer be blocked by grabbing that id). When taking multiple queries that's a huge contention reliever.
    – Nic
    Apr 27 '17 at 16:55
  • If the two tables require they be on the same sequence of numbers. Then conceptually, are they the same thing? Should they actually be in one table (perhaps with separate tables for additional attributes? An example would be putting "Call" and "Response" in separate tables, when they are all just "Messages"
    – AMtwo
    Apr 27 '17 at 17:59
  • Started going down the one table path, but for reasons too long to detail here, we're staying with two tables. Apr 27 '17 at 18:14

SELECT MAX(id)+1; is incorrect, will not work under even mild concurrency as multiple processes will find the same MAX and then attempt to insert the same next value.

A SEQUENCE is correct, so by definition better, but not necessarily the best. It should be used as a DEFAULT constraint on the column.

By far simplest solution is to simply use an IDENTITY column.

  • can an IDENTITY be shared between 2 tables (as the OP's description)? Apr 27 '17 at 16:59
  • 2
    @ypercubeᵀᴹ Din't catch that. Obviously, not. SEQUENCE still should be used as a DEFAULT constraint, not queried in a round-trip before INSERT. Apr 27 '17 at 17:00
  • I am refactoring existing tables where the primary key is not an IDENTITY. Conceptually I like a Sequence, but based on the current the current work load, the performance gain will not be that dramatic. Apr 27 '17 at 17:49

You could set identity columns to each hop two units, and set one as even numbers and the other as odd. int identity(1,2) for one table, int identity(2,2) for another table.

  • I like this idea for new tables, but I am refactoring existing tables. Apr 27 '17 at 17:52
  • 3
    My 2c: This seems very fragile. One operator error (a reseed, or an insert with identity_insert) and you end up with duplicates between the tables. Apr 27 '17 at 17:57
  • 1
    @RemusRusanu I agree that SEQUENCE is a better approach in SQL 2012+, but a check constraint to enforce odds/evens could make this approach less fragile and might be worth considering if for some reason you were stuck on SQL 2008 or earlier. Apr 27 '17 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.