Get unique sequential number- best practice

I need a number generator. (e.g. for Receipt number, or transaction number,
etc.) in a multiuser high volume envoironment. What is the best way to get
one from SQLserver2005? (no duplicates allowed)
1) I've seen a StoredProc that will get value, value++, then save back,
enclosed in a Transaction. This will work, but locks the table. A little
concerned about the blocking here.
2) Should I do the same without the Transaction and check for changed value
(optimistic lock?)
3) better way ?
Thanks!Look up identity columns. That should satisfy most of your requirements.
Anith|||Assuming you're not happy with the identity column property and for your own
reasons need this to be implemented with a stored procedure...
The locking part of the technique you are talking about is essential if you
need to guarantee no gaps in the sequence. You queue requests for a new
sequence value by locking it for the duration of the transaction. Here's an
example for an implementation of a blocking sequence:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.SyncSeq') IS NOT NULL
DROP TABLE dbo.SyncSeq;
GO
CREATE TABLE dbo.SyncSeq(val INT);
INSERT INTO dbo.SyncSeq VALUES(0);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_SyncSeq') IS NOT NULL
DROP PROC dbo.usp_SyncSeq;
GO
CREATE PROC dbo.usp_SyncSeq
@.val AS INT OUTPUT
AS
UPDATE dbo.SyncSeq
SET @.val = val = val + 1;
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The UPDATE statement in the stored procedure locks the sequence exclusively
and maintains the lock for the duration of the transaction. If running in
the context of an explicit transaction, the lock is maintained until the
explicit transaction finishes.
As an example, suppose connection 1 requests a new sequence value in an
explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is blocked:
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
Connection 2 gets the sequence value 1 because it was ultimately not used by
connection 1.
You see, if you want to guarantee that there won't be any gaps, you must
queue requests for new sequence values by locking the sequence for the
duration of the transaction.
If you don't care about gaps, rather only want to guarantee uniqueness of
sequence values, you can use a different sequencing logic, based on
identity. You can rely on the fact if a transaction is rolled back, identity
increment is not rolled back as it's not considered part of an explicit
transaction. Here's how you can implement the sequencing mechanism:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.AsyncSeq') IS NOT NULL
DROP TABLE dbo.AsyncSeq;
GO
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_AsyncSeq') IS NOT NULL
DROP PROC dbo.usp_AsyncSeq;
GO
CREATE PROC dbo.usp_AsyncSeq
@.val AS INT OUTPUT
AS
BEGIN TRAN
SAVE TRAN S1;
INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
SET @.val = SCOPE_IDENTITY();
ROLLBACK TRAN S1;
COMMIT TRAN
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The purpose of the transaction in the stored procedure is to allow defining
a savepoint and rolling back to it without effecting an external
transaction.
The rollback's purpose is to undo the insertion to the sequence table,
preventing the need to clear it from time to time for maintenance. Remember
that the identity increment is not effected by the rollback.
Back to the original example, suppose connection 1 requests a new sequence
value in an explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is not blocked, rather gets
the value 2:
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
At this point you have a gap in your sequence values since the value 1 was
ultimately not used, while 2 was. If you don't care about gaps, this
mechanism provides better concurrency.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Ronj" <Ronj@.discussions.microsoft.com> wrote in message
news:C1D283D7-2849-4E9A-8F93-6C1F7048128F@.microsoft.com...
>I need a number generator. (e.g. for Receipt number, or transaction number,
> etc.) in a multiuser high volume envoironment. What is the best way to get
> one from SQLserver2005? (no duplicates allowed)
> 1) I've seen a StoredProc that will get value, value++, then save back,
> enclosed in a Transaction. This will work, but locks the table. A little
> concerned about the blocking here.
> 2) Should I do the same without the Transaction and check for changed
> value
> (optimistic lock?)
> 3) better way ?
> Thanks!|||>> I need a number generator. (e.g. for Receipt number, or transaction numbe
r, etc.) in a multiuser high volume envoironment. <<
What kidn of check digit and validatoin are you using? Is this number
exposed in such a way that your need a SOX audit trail? People think
this can be done on one machine with IDENTITY and it really is not that
esy, if you give a damn about doing it right. What IDENTITY says is
that you are planning on never being a large company with many stores
on purpose! The gps will not matter because nobody will ever invest in
the company so there is no need for good auditing and SOX compliance!
Not a great business plan.
Not a problem, really. You can issue blocks of invoice numbers to
stores/salesmen or you can have a generator rule that adds the store,
cash register, timestamp and a sequence number to the sales ticket
(works for Home Depot, et al).
(optimistic lock?) <<
With a computed key like the Home Depot (they are on my mind today --
I just bought some keys), optimistic concurrency control (it is not
really locking) works great. But SQL Server is a pessimistic system by
nature. What to use Firebird or Innerbase instead?
Look up additive congruence generators if you need a random number that
will not repeat. There are some games you can play with those that are
fun.
Again, there is no "Magic, Universal one-size-fits-all" answer. Ever
wonder why each industry has different standards? Different problems!