Sequence generator

[newtophp2000]

Hello,

Since SQL Server has no sequence generator, I wrote my own. (I claim
no ownership of it as it is closely modeled after earlier discussions
on this topic.) I have included the sql statements below.

While it works independently on its own, It seems to lock in multi-user
environments or in nested-transactions. It s funny really: I have my
main transaction, but the sequence generator below forces another
transaction, which I do not really care for. I cannot remove the extra
transaction from the sequence generator because I would like to discard
any values it retrieved, regardless of whether the main transaction
succeeded or failed.

Any suggestions?
--------------------------------------------------------------------------------
create table my_sequence (name varchar(10), seq int identity (1, 1))
go

declare @next int
begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

---------------------------------------------------------

[Erland Sommarskog]

(ne**********@yahoo.com) writes:

Since SQL Server has no sequence generator, I wrote my own. (I claim
no ownership of it as it is closely modeled after earlier discussions
on this topic.) I have included the sql statements below.

While it works independently on its own, It seems to lock in multi-user
environments or in nested-transactions. It s funny really: I have my
main transaction, but the sequence generator below forces another
transaction, which I do not really care for. I cannot remove the extra
transaction from the sequence generator because I would like to discard
any values it retrieved, regardless of whether the main transaction
succeeded or failed.

--------------------------------------------------------------------------
------
create table my_sequence (name varchar(10), seq int identity (1, 1))
go

declare @next int
begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

That should not even work; you cannot update an IDENTITY column.

As for the nested transaction, all that happens when you commit a
nested transaction is that the transaction counter is decremented.
No locks are released, and nothing is committed for real.

Itzik Ben-Gan had an article on this some issues back in SQL Server
Magazine. I seem to recall that he suggested a sequence generator
that went something like:

SAVE TRANSACTION seq
INSERT tblwithidentity DEFAULT VALUES
SELECT @seq = @@identity
ROLLBACK TRANSACTION seq

The point here is that since sets up a save point and rolls back, the
table remains available, and other processes are not blocked. At the
same time, you know that you will not get the same value twice,
because the generation of the IDENITTY value is not rolled back.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

> declare @next int

begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

If you can consume numbers in batches, do it, as it is dramatically
more efficient. For instance, if you know up front that you need 100
numbers, do this:

update my_sequence set seq = seq + 100 where name = 'abc';
select @next = seq - 99 from my_sequence where name = 'abc';

[Mike C#]

Doesn't work. You can't update an identity column.

<ne**********@yahoo.com> wrote in message
news:11**********************@r2g2000cwb.googlegro ups.com...

Hello,

Since SQL Server has no sequence generator, I wrote my own. (I claim
no ownership of it as it is closely modeled after earlier discussions
on this topic.) I have included the sql statements below.

While it works independently on its own, It seems to lock in multi-user
environments or in nested-transactions. It s funny really: I have my
main transaction, but the sequence generator below forces another
transaction, which I do not really care for. I cannot remove the extra
transaction from the sequence generator because I would like to discard
any values it retrieved, regardless of whether the main transaction
succeeded or failed.

Any suggestions?
--------------------------------------------------------------------------------
create table my_sequence (name varchar(10), seq int identity (1, 1))
go

declare @next int
begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

---------------------------------------------------------

[newtophp2000]

Erland Sommarskog wrote:

That should not even work; you cannot update an IDENTITY column.
You are right, of course. That was an error on my part when copying
and pasting from the query window. It was a plain INT field.

The point here is that since sets up a save point and rolls back, the
table remains available, and other processes are not blocked. At the
same time, you know that you will not get the same value twice,
because the generation of the IDENITTY value is not rolled back.

I am running some tests to see how it works, but so far, things look
great!

Thanks a lot for the great tip!

[newtophp2000]

Alexander Kuznetsov wrote:

declare @next int
begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

If you can consume numbers in batches, do it, as it is dramatically
more efficient. For instance, if you know up front that you need 100
numbers, do this:

update my_sequence set seq = seq + 100 where name = 'abc';
select @next = seq - 99 from my_sequence where name = 'abc';

This looks interesting. A suggestion that was very similar to this one
came up a while ago in our project. As I remember, the developers did
not like the idea as it introduced more work for them to manage.

Thanks for the tip!

[Madhivanan]

Where do you want to show the data?
If you use front end application, do the numbering there

Madhivanan
ne**********@yahoo.com wrote:

Alexander Kuznetsov wrote:

declare @next int
begin transaction
update my_sequence set seq = seq + 1 where name = 'abc';
select @next = seq from my_sequence where name = 'abc';
commit transaction

If you can consume numbers in batches, do it, as it is dramatically
more efficient. For instance, if you know up front that you need 100
numbers, do this:

update my_sequence set seq = seq + 100 where name = 'abc';
select @next = seq - 99 from my_sequence where name = 'abc';

This looks interesting. A suggestion that was very similar to this one
came up a while ago in our project. As I remember, the developers did
not like the idea as it introduced more work for them to manage.

Thanks for the tip!