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
--------------------------------------------------------- 6 15017
(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
> 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';
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
---------------------------------------------------------
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!
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!
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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):...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
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...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
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...
| |