Efficient way to generate sequential numbers in SQL Azure Federations

Question

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

For example, for 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 this tenant, the OrderId should be increased. For example, for tent A OrderId will be 1, 2, 3 ... and for tenant B OrderId will also be 1, 2, 3 ... in an independent sequence. Ideally, there should be no spaces.

TenantId and OrderId are components of the primary key. Their values ​​are set by the application, and they are not related to the problem of generating sequences; only OrderId has a serial number with a business value. In addition, TenantId is a federation distribution key.

This MSDN blog article describes in Option 1 an approach to having a table containing sequences, and using a stored procedure in a separate transaction to increase the sequence. Each tenant must have an entry in this table containing the last used sequence value.

Will this be the best approach to scalability, competition, resource blocking? Any other useful tricks given the limitations of SQL Azure Federations?

Answer 1

Here are 2 more ideas.

The approach should be to have a separate process updating this field to make it asynchronous, if possible for your business scenario. This approach requires the OrderNumber field to be NULL. To find out which order came first so that it received the correct OrderNumber, I would also add the InsertedDate field. Asynchronous processing becomes more complex if you have several work roles that fulfill this responsibility for redundancy, in which case you need each process to assign itself the records it is working on (so you also need the OwnedBy field), add concurrency a test during UPDATE to ensure that each process runs on its own records and the record assignment expires (so you also need the AssignedOn field) if the process fails so that it does not leave orphans. Not entirely trivial ...

And then you have a poor man's approach ... which, when the stars align quite well, may be all you need. If you want to have an optimistic concurrency approach, try using the following number during insertion (first select the MAX order number for this TenantId and OrderId) and then run the Insert command. If the insertion fails (because you added a unique index to TenantId, OrderNumber for this purpose), just add 1 to OrderNumber. The real problem here is the frequency of retries and the likelihood that this approach will fail. If you have a relatively streamlined business process, this may not actually fail; if you have orders added constantly from several paths, this may be an unacceptable approach.

Answer 2

Not sure how much effort will be required for your scenario, but look at this and see if you can configure it: SnowMaker - a unique id generator for Azure (or any other cloud hosting environment)