Threadsafe generation of sequence numbers with stored procedures

Posted on 2003-03-24
Medium Priority
Last Modified: 2012-06-27
I need to generate a sequence number for new entries in a table of Work Orders.
Each additional entry should get a unique sequence number.
The Sequence number should be a string with exactly seven characters.
Example: 0000001, 0000002, ... , 0001034, ...
The pocedure must be threadsafe,
so if multiple threads are calling the sql server at once it should still work reliably.
I would like to call the stored procedure from within vba.
It should work with MSSQL Server.
And it should be reasonably fast for large tables with about 100.000 entries.
It would be nice if the procedure could return the newly generated sequence number.
I think that's all.
Thanks for suggestions,
Question by:alexwms
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1

Expert Comment

ID: 8201577
You can use an id column to generate a number, a function to convert that to a 7 character field and finally a computed column to hold the result.

CREATE FUNCTION dbo.prepend7zeroes (
     @id int
RETURNS char(7) AS  
     declare @result char(7)
     set @result = convert(char, @id)
     while len(@result) < 7
          set @result = '0' + @result
     return @result

CREATE TABLE workorders (
     id int IDENTITY (1, 1) NOT NULL
     , seqno as dbo.prepend7zeroes(id)
     , field1 varchar(50)

create procedure workorders_insert
     @field1 varchar(50)
insert into
     workorders (
     ) values (
     id = scope_identity()

-- Testing
exec workorders_insert 'test'
LVL 13

Accepted Solution

ispaleny earned 300 total points
ID: 8201736
-- One operation, update locking
insert YourTable(SeqCol)
select right('0000000'+cast(cast(max(SeqCol) as int)+1 as varchar(7)),7)
from YourTable (UPDLOCK)

-- Returns new value, select+update locking
begin tran
 declare @NewVal char(7)
 select @NewVal=right('0000000'+cast(cast(max(SeqCol) as  int)+1 as varchar(7)),7)
 from YourTable (TABLOCKX)
 insert YourTable(SeqCol) values (@NewVal)
 select @NewVal

The creation of SP is very simple now :)
LVL 12

Expert Comment

ID: 8201844
can you use mssql build in threadsafe ?

intId int IDENTITY (1, 1) NOT NULL ,
CharId AS (right(('0000000'+ convert(varchar(7),intId)),7)),
value char (10)  NULL

insert into test ( value) values ( 'first')

select right(('0000000'+ convert(varchar(7),IDENT_CURRENT('test'))),7)

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Expert Comment

ID: 8202122
Identity is a great feature, but cannot be used for generating numbers in sequence.
If you insert new record and an error occurs (constraint, timeout, deadlock,...), a transaction is rolled back, but an identity number is used !!! It will leave gaps.
LVL 12

Expert Comment

ID: 8202188
i live with gaps in my identity columns for several years without problems.
should i have some?
LVL 13

Expert Comment

ID: 8202489
do you need {1,2,3,4} or {1,2,4,5} is satisfactory ?

Author Comment

ID: 8203008
Hello together,
thanks for the suggestions, identity was also my first thought but I prefered the style with a fixed number of characters.
So using identity will always leave me with this extra column and that just doesn't feel right. But maybe it's the way to go.

I like ispaleny's suggestion because it avoids this second column.

I'm not sure about the speed implications of using max() here. I guess the use of identity as a build in function is faster. Also it has the advantage of being nonblocking.

I'm also unsure about the use of two seperate statements inside of the commit statement. Will any looks of single statements inside a commit be global. Otherwise this does look dangerous.

About the gaps. Even ispaleny's suggestion will leave gaps if a thread requests a sequence number while another thread is running and rolled back later, right?
Fortunately it's not necessary to enforce a continous numeration in my case.

Is it possible to splitt points or award points directly?

Thanks for the ideas so far,
LVL 13

Expert Comment

ID: 8203302
To "Even ispaleny's suggestion will leave gaps..."
I must say no, used locks will prevent other users to insert records above currently being inserted ones.

To "I'm not sure about the speed implications of using max() here"
You can use "select top 1 SeqCol ... order by SeqCol DESC", but SeqCol is a primary key. The maximal value is taken from statistics.

Author Comment

ID: 8203351
So instead of gaps any request of a new batch number will fail while any transaction which uses a new batch number will take place?
Some of the transactions tied to a batch number take fairly long.
Or will it be like that that the transaction might fail but I will be left with an orphan seq. number?

begin transaction
@BatNbr= sp_getSeqNbr
call sp_other(@BatNbr)
commit transaction

Or should I handle this differently?
LVL 13

Expert Comment

ID: 8203579
In MSSQL you can name nested transactions, but it is usable for named rolling back. If you need to use a high level lock in transaction, give it in the end to be as short as possible.

--Table is locked for all 10 s
begin tran  MegaOp
begin tran  MiniOp
declare @Max char(10)
select @Max=max(SeqCol) from YourTable (TABLOCKX)
WAITFOR DELAY '00:00:05'
commit tran MiniOp
WAITFOR DELAY '00:00:05'
commit tran MegaOp

Author Comment

ID: 8208018
From what I understand the locks enable You to ensure a continous numeration. Of course the price is a serialization of all transactions that would require a seq. number.
There is always the possibility to seperate the creation of seq. numbers from the transaction. This would then lead to orphan seq. numbers in case of rolled back transactions. These orphan seq. numbers could be deleted later leaving the gaps in the numbering if there was another transaction in the meantime.

In my case I will just allow orphan seq. numbers and seperate the creation of seq. numbers from the transaction itself to minimize locking of the seq. number table.

Thanks for the insightfull answers,

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question