Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best code for generating sequence numbers


Best code for generating sequence numbers

Author
Message
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 494
Jeff Moden (2/6/2010)

Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.


That is the best commented bit of SQL I've ever read. Kudos to you Jeff, you're work must be a DREAM to maintain! I one day hope I run into one of your projects, for two reasons: 1. I'll probably not need to do anything (ever), and 2. if I do I'll know exactly what you were trying to do!

From now on, I'm writing that level of commenting in all my code. Awesome!

Random Technical Stuff
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
ta.bu.shi.da.yu (10/21/2010)
Jeff Moden (2/6/2010)

Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.


That is the best commented bit of SQL I've ever read. Kudos to you Jeff, you're work must be a DREAM to maintain! I one day hope I run into one of your projects, for two reasons: 1. I'll probably not need to do anything (ever), and 2. if I do I'll know exactly what you were trying to do!

From now on, I'm writing that level of commenting in all my code. Awesome!


That's an awesome compliment, ta.bu.shi.da.yu. Thank you for your kind words.

By the way (I've always wanted to ask)... What does "ta.bu.shi.da.yu" mean?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 494
Jeff Moden (10/21/2010)
ta.bu.shi.da.yu (10/21/2010)
Jeff Moden (2/6/2010)

Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.


That is the best commented bit of SQL I've ever read. Kudos to you Jeff, you're work must be a DREAM to maintain! I one day hope I run into one of your projects, for two reasons: 1. I'll probably not need to do anything (ever), and 2. if I do I'll know exactly what you were trying to do!

From now on, I'm writing that level of commenting in all my code. Awesome!


That's an awesome compliment, ta.bu.shi.da.yu. Thank you for your kind words.

By the way (I've always wanted to ask)... What does "ta.bu.shi.da.yu" mean?


Oh man... it actually doesn't mean anything at all really. In Mandarin it literally means "He/she is not a big fish". I first used it when I was learning that - it was actually in a Mandarin language book but was a list of words... I used it on Kuro5hin.org as a throwaway account, then I used it on Wikipedia and actually it turns out that I became fairly famous for it (I invented "[Citation needed]"... true story).

Now I'm learning about SQL Server, and my Wikipedia administration days are way behind me. Of course, I registered this name without realising that on SQLServerCentral everyone uses their real name... kind of stuck with it now!

My real name, btw, is Chris.

Random Technical Stuff
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
ta.bu.shi.da.yu (10/21/2010)
Jeff Moden (10/21/2010)
ta.bu.shi.da.yu (10/21/2010)
Jeff Moden (2/6/2010)

Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.


That is the best commented bit of SQL I've ever read. Kudos to you Jeff, you're work must be a DREAM to maintain! I one day hope I run into one of your projects, for two reasons: 1. I'll probably not need to do anything (ever), and 2. if I do I'll know exactly what you were trying to do!

From now on, I'm writing that level of commenting in all my code. Awesome!


That's an awesome compliment, ta.bu.shi.da.yu. Thank you for your kind words.

By the way (I've always wanted to ask)... What does "ta.bu.shi.da.yu" mean?


Oh man... it actually doesn't mean anything at all really. In Mandarin it literally means "He/she is not a big fish". I first used it when I was learning that - it was actually in a Mandarin language book but was a list of words... I used it on Kuro5hin.org as a throwaway account, then I used it on Wikipedia and actually it turns out that I became fairly famous for it (I invented "[Citation needed]"... true story).

Now I'm learning about SQL Server, and my Wikipedia administration days are way behind me. Of course, I registered this name without realising that on SQLServerCentral everyone uses their real name... kind of stuck with it now!

My real name, btw, is Chris.


If you ever decide to use your real name here, all you have to do is go into your profile and change it.

I have to say again... thank you for the great compliment, Chris. And I've also seen your blog. You should include the URL for it in your signature line. Again, that's in your profile.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
lcasamen
lcasamen
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
So I realize this topic is a bit old but I'm in the middle of converting a java app from Oracle to SQLServer and of course there is a ton of bad code that fetches the next value and then uses it to create data - sometimes creating the child before the parent ( ugh). Of course there is virtually no time to get this converted. I've got 20 years of Oracle experience but am new to SQLServer - and seriously worried about the difference in locking behavior.
Anyway - we ran the conversion tool from MSDN and it came up with this for sequence generation:
1. create 1 table per sequence - ie
create table Z_A_SEQ_STBL(nextval numeric(10, 0) identity(5000,1) NOT NULL);
2. create this proc:
create procedure SL_A_SEQ_NEXTVAL(@nextval [numeric](10, 0) out) as BEGIN insert into Z_ACCESSORY_SEQ_STBL default values set @nextval = scope_identity() END
;

Doesn't look nearly as robust as the one in this thread. What do you guys think?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
lcasamen (2/7/2011)
So I realize this topic is a bit old but I'm in the middle of converting a java app from Oracle to SQLServer and of course there is a ton of bad code that fetches the next value and then uses it to create data - sometimes creating the child before the parent ( ugh). Of course there is virtually no time to get this converted. I've got 20 years of Oracle experience but am new to SQLServer - and seriously worried about the difference in locking behavior.
Anyway - we ran the conversion tool from MSDN and it came up with this for sequence generation:
1. create 1 table per sequence - ie
create table Z_A_SEQ_STBL(nextval numeric(10, 0) identity(5000,1) NOT NULL);
2. create this proc:
create procedure SL_A_SEQ_NEXTVAL(@nextval [numeric](10, 0) out) as BEGIN insert into Z_ACCESSORY_SEQ_STBL default values set @nextval = scope_identity() END
;

Doesn't look nearly as robust as the one in this thread. What do you guys think?




If you're able to, forget about sequence tables in SQL Server. Use an IDENTITY column on the tables, instead.

I will say, however, that if you don't mind the table growing, you can certainly use the method you've shown without much chance of a deadlock. Of course, that would also be true for an IDENTITY column on the final table.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9505
The forthcoming version of SQL Server, codenamed "Denali" will have a sequence type object that functions similar to Oracle's sequence. However, just like an identity, it can also result in gaps if a transaction rollback occurs during insert.

Even a custom solution involving a tally table can result in gaps, unless you serialize the entire transaction of retreiving the next ID, inserting the record(s), and then updating the next available ID. It has to be serialized (blocking), because allowing another process to grab the next ID in the interim, before the first process has successfully committed it's transaction, will inevitably result in a gap when rollbacks occur.

Really, I think that it would be best to re-factor whatever application functionality or business rule requires that the numbers be entirely consecutive with no gaps, and ideally this would involve going with a standard identity solution.

Another reason developers sometimes resort to using a custom ID sequencer is when an ID is shared across multiple tables, and each table needs a non-overlapping range of values. In this case you can still use an identity, but also use an appropriate identity seed and check constraint placed on each table.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
lcasamen
lcasamen
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Thanks for the quick replies! I don't need to worry about gaps in sequences - Oracle loses values on rollback too, and I just need matching functionality. Long term we were planning to migrate to identities, just too much code to do by the delivery date - but good to know sequences are coming.

I'm sure I'll be back.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9505
lcasamen (2/8/2011)
Thanks for the quick replies! I don't need to worry about gaps in sequences - Oracle loses values on rollback too, and I just need matching functionality. Long term we were planning to migrate to identities, just too much code to do by the delivery date - but good to know sequences are coming.
I'm sure I'll be back.

I didn't even realize this was the continuation of an thread from back in 2009. Re-reading your post from yesterday, you also mention the following:
...there is a ton of bad code that fetches the next value and then uses it to create data - sometimes creating the child before the parent...

It sounds as if there are currently no foreign key constraints declared to prevent orphan rows from being inserted into child tables. If creating foreign keys isn't already on your to-do list for this database re-factoring project, then I'd definately suggest that as well. Of course this would also involve retrofitting all the procedures that are attempting to insert orphan rows.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
wmcgeorge
wmcgeorge
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 63
Quick word of caution: TRUNCATE TABLE will reset IDENTITY() to its original seed value. I just got burned by that feature when I used truncation instead of deletes to empty a table.
Crazy

Great question, by the way! It's sad that we had to wait until SQL Server 2012 to get CREATE SEQUENCE...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search