Jan 18

Generating Consecutive Numbers Within a Given Range

A couple of months ago I was working on a project where we were revamping an existing system.  One piece of the system was a call to a stored procedure that generated a number in sequence and assigned it to a user that was accessing the application. It was very similar to taking a number and waiting in line. In that respect, it was also similar because we were required to have a range of numbers used for this.  In our case it was to start at 100000 and then rollover back to 100000 once we got to 899999.

When I began this project, an existing stored procedure was accomplishing this in a peculiar way.  We only noticed a problem because of newly required load testing. When pushed to a high amount of calls to this stored procedure, they started failing. We found it was because the existing script was using the SERIALIZABLE isolation level as it first grabbed the MAX value from a 1 row/1 column table, checked if it was less than 899999, if it was it added 1 to it, updated it the table with the new value, and then passed back the value to the application. If it was equal to 899999, it updated the value in the table to 100000.  This isolation level created deadlocks when the stored procedure was called many times in quick succession which caused it to fail.  Subsequently, the application then failed as well. (I will cover isolation levels with more detail in a future post.) This method also did not keep a history of the numbers and thereby made it very difficult to trace back to a particular user, vendor, or time.

The Solution!

I wanted a solution that would not cause deadlocks, but that would also guarantee a unique number each time the stored procedure was called. I also wanted to store a history of when these numbers were generated, yet somehow be able to identify numbers after rollovers in the range.  I knew that generating sequential numbers in a range this large would not rollover for many years, so as long as the numbers could be generated sequentially I would not have any problems rolling over.

We decided to use a log table with an identity column, and a date column to start.

gNumber int IDENTITY(1,1) NOT NULL,
gTime datetime NOT NULL,
gNumber ASC

This allowed us to allow SQL to generate the number sequentially without having any chance of repeating the same number. Skipping numbers was also not an issue. So how did we limit the range?

CREATE PROC generateNumber
INSERT INTO numberLog(gTime)
SELECT scope_identity() % 800000 + 100000

This stored procedure will maintain the identity in the table while using the newly created id to return the proper number to the application.  Since the application itself never needs to return to this table, it was the perfect solution!

How does it work?

The table is set to auto-increment the first column upon insertion.  The SQL Server Database Engine ensures that this value is unique and incremented sequentially upon insertion.  So, when the stored procedure is called to generate a number, it first inserts a row into our table. Upon insertion, the identity is generated and the current datetime is inserted with it by using the GETDATE() function. Finally, the identity value is selected by  using the SCOPE_IDENTITY() function which returns the generated identity only from the scope of this session/stored procedure.  That identity is then converted using the % and + operator to give us the number required by the application. The % operator gives us the integer remainder of the identity number divided by 800000. Below is a sample of what happens with different numbers using the % operator.

SELECT 0 % 800000 --0
SELECT 1 % 800000 --1
SELECT 799999 % 800000 --799999
SELECT 800000 % 800000 --0
SELECT 800001 % 800000 --1

The great thing about this method is that we now have a log of generated numbers that never rolls over and by using the generated number in conjunction with the formula we can solve for multiple identity numbers.  Knowing the approximate date that the number was generated will give us the specific identity.  Again, it was not a requirement to store the generated numbers or the dates, but I thought it was safer to do so and may make my life easier if they decide to do a lookup.

Leave a Reply