Tag Archives: sql server

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",

    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!

Could not load file or assembly ‘Microsoft.AnalysisServices, Version=

If you’re getting the following error upgrading TFS 2012 RTM to ver. 2012.3:

TF255356: The following error occurred when configuring the Team Foundation databases:
TF400711: Error occurred while executing servicing step Upgrade Warehouse for component UpdateWarehouseVersion during FinishInstallUpdates: Could not load file or assembly ‘Microsoft.AnalysisServices, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91‘ or one of its dependencies. The system cannot find the file specified.. For more information, see the configuration log.

then just install Microsoft SQL Server 2012 Analysis Management Objects from Microsoft SQL Server 2012 Feature Pack (expand the Install Instructions node).

Note that you may need to restart tge setup wizard, or even server itself before error will gone.