Using SQL Sequence

Recently we have a requirement for one project where the ID for any entities in this project must be within a range of number. In this project, we have to integrate with a number of other external parties. The ID for any entity used in this integration must be unique globally across all the connected parties. For various reasons, other parties can only use number as IDs, otherwise this problem can easily be solved by using GUID. Each party will have a reserved range of number which they can use as ID for their entities. For example,

Party A: 1 -> 300 000 000 000

Party B: 300 000 000 001 -> 600 000 000 000

Party C: 600 000 000 001 -> 900 000 000 000

First solution that came to mind was using identity column in SQL table and set the starting value to be the range minimum value. However, since we also need to store the entities created by other parties in our system, this won’t work as their IDs would be outside of our allowed range.

We could solve the above problem with having a separate table for each entity just for the purpose of generating this sequential ID. This table will only have the identity column. Before we insert into the entity table, we insert into this ID table first and then we query it to get the sequential ID that was inserted.

That solution also seems tedious and requires too many tables just for the ID. It also requires additional trips to the database in order to get the next ID. One of the developers suggested an even better idea to solve this without the need for additional tables, using Sequence. I worked with SQL Sequence before so when I heard about it I knew that would be the best fit for us.

SQL sequence is a SQL object that generates a sequence of numeric values according to the specification. To create a sequence, you can execute the below SQL

CREATE SEQUENCE BookIdSequence  
    START WITH 300000000001
    INCREMENT BY 1
    MINVALUE 300000000001
    MAXVALUE 600000000000
    NO CYCLE;  -- This means an exception will be thrown if sequence exceeds its max value. CYCLE will starts from the beginning
GO  

Alternatively, you can create sequence using Code First in EF

protected override void OnModelCreating(ModelBuilder modelBuilder)
{       
   modelBuilder
       .HasSequence<long>("ExternalBookIdSequence")
       .StartsAt(300000000001)
       .IncrementsBy(1)
       .HasMin(300000000001)
       .HasMax(600000000000)
       .IsCyclic(false);
}

There are 2 ways to consume the sequence. There are times when you might need to get the next ID upfront before inserting into the table. In that case, you can run the below query to get the next value. This requires one additional trip to the database.

SELECT NEXT VALUE FOR BookIdSequence

The other way is to use this sequence as the default value for the Id in the table. For those entities created by other parties, use their ID when inserting into the table. For those created by us, we would leave this property as null and let SQL uses the sequence as default. This will require no additional trip to the database.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{       
   modelBuilder
       .HasSequence<long>("ExternalBookIdSequence")
       .StartsAt(300000000001)
       .IncrementsBy(1)
       .HasMin(300000000001)
       .HasMax(600000000000)
       .IsCyclic(false);

   modelBuilder
       .Entity<Book>()
       .Property(x => x.ExternalBookId)
       .HasDefaultValueSql("NEXT VALUE FOR ExternalBookIdSequence");
}