counter/sequence in database

Hi all,
I'm looking for some common patterns on counters (or sequences) implemented
in database.
I need several sequences with definable range. It should be function -
something like GetNext(SequenceType). I need to call it from within
potentially concurrently running transactions so that transactions would not
block each other (calling GetNext should not participate in callers'
transactions, it does not matter if there will be gaps in sequence caused by
rollbacking of caller transaction, but the sequence should run in its own
serializable transaction to ensure not return the same value to concurrent
calls).
Thanks for tips,
eXavierIn T-SQL you cannot have a piece of code called within a transaction and not
be a part of an external transaction.
Also, you cannot change the state of a database from a user defined
function.
But if you simply need an "independent" non-blocking key generator that is
not tied to a particular table, you can use the following sequence table and
stored procedure:
-- infrastructure
create table seq(val int not null identity)
go
create proc usp_getseq @.val as int output
as
begin tran
save tran s1
insert into seq default values
rollback tran s1
set @.val = scope_identity()
commit tran
go
-- To use
declare @.i as int
exec usp_getseq @.i output
select @.i
The trick is to load a row to a table, which will generate a new identity
value. You rollback the activity (to a savepoint, not to affect the external
transaction) meaning the logically no row will ever enter the table. Still,
the identity value advances, and you can return it via scope_identity().
Identity values are generated in a non blocking mode, so no concerns about
concurrency here.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"eXavier" <fhns@.centrum.cz> wrote in message
news:OGSX2B9fFHA.2444@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm looking for some common patterns on counters (or sequences)
> implemented
> in database.
> I need several sequences with definable range. It should be function -
> something like GetNext(SequenceType). I need to call it from within
> potentially concurrently running transactions so that transactions would
> not
> block each other (calling GetNext should not participate in callers'
> transactions, it does not matter if there will be gaps in sequence caused
> by
> rollbacking of caller transaction, but the sequence should run in its own
> serializable transaction to ensure not return the same value to concurrent
> calls).
> Thanks for tips,
> eXavier
>