473,396 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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 15018
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: alr | last post by:
I need to repeat each item in a list n times, like this function does: def repeatitems(sequence, repetitions): newlist = for item in sequence: for i in range(repetitions):...
4
by: ahsan Imam | last post by:
Hello All, I am trying to move an application from python 1.5.2 to 2.3. The code works fine in 1.5.2 but gives the exception (exceptions.TypeError unpack non-sequence) in python 2.3. I did not...
6
by: James Liu | last post by:
I did some search and didn't find anything about whether Yokon will support sequence object. Appreciate it if you can reply with some resources. Thanks, James
3
by: pluton | last post by:
Hallo, Hot to get unique, sequential number during execution of stored procedure ? I can create table with autoincrement column, add record, get ident_current and delete record each time i...
4
by: Nel | last post by:
Hi all, I am driving myself crazy trying to think of a solution to this - perhaps one of you guys can see a simple solution. The problem is displaying the contents of an array in what appears...
4
by: Tim McAuley | last post by:
Hi, I've hit a little problem and was wondering if anyone might be able to give some help. Set-up: - JBoss appserver using entity beans to access database - Using sequence pattern to...
0
by: Kamran K | last post by:
Hello I have created a client server application using C#. Existing application is using random number on client side to generate sequence numbers that are then assigned to transactions. This...
12
by: Jim Michaels | last post by:
I need to generate 2 random numbers in rapid sequence from either PHP or mysql. I have not been able to do either. I get the same number back several times from PHP's mt_rand() and from mysql's...
28
by: Rob Cowie | last post by:
Hi all, I wish to generate a sequence of the form 'aaa', 'aab', aac'.... 'aba', 'abb', 'abc' etc. all the way to 'zzz'. How would you construct a generator to acheive this? A simple,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.