Efficient way to generate sequential numbers on SQL Azure Federations

Question

Considering that SQL Azure Federations does not support the IDENTITY property or SEQUENCEs, what would be an efficient way to generate sequential numbers when inserting records?

For instance, given a table with these columns:

CREATE TABLE [dbo].[Orders] (
    [TenantId] [uniqueidentifier] NOT NULL,
    [OrderId] [uniqueidentifier] NOT NULL,
    [OrderNumber] [int] NOT NULL
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED (
        [TenantId] ASC,
        [OrderId] ASC
    )
) FEDERATED ON ([FederationKey] = [TenantId])

for each order inserted for a given tenant, the OrderId should be incremented. For instance, for tentant A OrderId would be 1, 2, 3... and for tenant B OrderId would also be 1, 2, 3... in an independent sequence. Ideally there should be no gaps.

TenantId and OrderId are components of the primary key. Their values are set by the application and they're not related to the issue of generating sequences; only OrderId has the sequential number with business meaning. Also, TenantId is the distribution key of the federation.

This MSDN Blog article describes in option 1 an approach of having a table holding the sequences and using a stored procedure in a segregated transaction to increment the sequences. Each tenant would have a record on this table holding the last used value of the sequence.

Would that be the optimum approach considering scalability, contention, resource locking? Any other useful tricks, considering the limitations of SQL Azure Federations?

Inaccessible

Answer 1

Here are 2 additional ideas.

An approach would be to have a separate process update that field to make this asynchronous, if that is something possible for your business scenario. You would need to have the OrderNumber field accept NULL values for this approach. To know which Order came first, so that it gets the correct OrderNumber, I would also add an InsertedDate field. Async processing gets more complex if you have multiple worker roles performing this duty for redundancy, in which case you will need to have each process assign itself the records it is working on (so you also need an OwnedBy field), add a concurrency test during the UPDATE to ensure each process is executing on its own records, and have the records' assignment expire (so you also need an AssignedOn field) if the process crashes so that it doesn't leave orphans. Not really trivial...

And then you have the poor's man approach... which when the stars align themselves just well enough may be all you need. If you are willing to have an optimistic concurrency approach, try using the next Number during the insert (select the MAX OrderNumber first for a given TenantId and OrderId), then perform the Insert. If the insert fails (because you added a Unique index on TenantId, OrderNumber for that purpose), just add 1 to the OrderNumber. The real issue here is the frequency of retries and the likelyhood of this approach failing. If you have a relatively streamlined business process this may actually never fail; if however you have orders added constantly from multiple avenues, this may be an unacceptable approach.

Birmingham

Great answer, in addition to the option I quoted in my question (using a stored procedure to update a record using a separate connection), your first alternative provides great scalability and throughput at the cost of some complexity; the second alternative is quite simple and might be enough when the process is streamlined (e.g. using a message queue) or when the rate of new order creation per each tenant is not too high. – Inaccessible

Answer 2

not sure how much effort would be required to fit your scenario, but have a look at this as well and see if you can tweak it: SnowMaker – a unique id generator for Azure (or any other cloud hosting environment)

Woollyheaded

That's a great resource. Thank you for bringing it to my attention. Local batching can be quite effective and scale well, at the cost of having gaps in the sequence and numbers out of chronological order, which is pretty much OK for internal IDs but raises (unfounded) concerns when applied to business control numbers. – Inaccessible

Would you still recommend using this? – Foppery