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!