How to generate ids programmatically using SQL Server and Entity Framework

TL;DR: create a sequence and execute stored proc to read from it

Generating ids in the database is used to be the default and near to the only one approach but now days this trend is fading away. Here’s some scenarios discovered by me recently where it doesn’t play well:
CQRS (see the comments, also Steven’s blog)
– bulk insert into many-to-many mapping table (see my previous post)

So here’s a solution. First, create a sequence (available in SQL Server 2012 or later, and in SQL Azure):

create sequence dbo.OrderId as int
start with 1
increment by 1

Then, read from it using the built-in stored procedure:

exec sys.sp_sequence_get_range @sequence_name, @range_size, @range_first_value OUT" 

And finally, execute the stored procedure programmatically. In this particular example, using Entity Framework:

 public async Task<int> GetSequenceStart(string sequenceName, int count)
{
    var sequenceNameParam = new SqlParameter
    {
        ParameterName = "@sequence_name",
        SqlDbType = SqlDbType.NVarChar,
        Direction = ParameterDirection.Input,
        Value = sequenceName
    };
    var countParam = new SqlParameter
    {
        ParameterName = "@range_size",
        SqlDbType = SqlDbType.Int,
        Direction = ParameterDirection.Input,
        Value = count
    };
    var outputParam = new SqlParameter
    {
        ParameterName = "@range_first_value",
        SqlDbType = SqlDbType.Variant,
        Direction = ParameterDirection.Output
    };

    await _dbContext.ExecuteSqlCommandAsync(
        "exec sys.sp_sequence_get_range @sequence_name, @range_size, @range_first_value OUT",
        sequenceNameParam,
        countParam,
        outputParam);

    return (int)outputParam.Value;
}

The drawbacks of this solution is that it doesn’t scale that well:
– if you’re using more than one database, you have to have one of them as a master and it will receive higher load.
– each application instance must be fully aware of such hierarchy what crosses the boundaries of horizontal scaling (aka scaling out).

Happy sequencing!

This entry was posted in Programming and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.