469,148 Members | 1,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Sequence generator

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

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

Jun 19 '06 #1
6 14824
(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
Jun 19 '06 #2
> 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';

Jun 19 '06 #3
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

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

Jun 20 '06 #4
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!

Jun 20 '06 #5
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!

Jun 20 '06 #6

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!


Jun 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by pluton | last post: by
4 posts views Thread by Nel | last post: by
4 posts views Thread by Tim McAuley | last post: by
reply views Thread by Kamran K | last post: by
12 posts views Thread by Jim Michaels | last post: by
28 posts views Thread by Rob Cowie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.