Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Best code for generating sequence numbers Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 4:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 258, 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
Post #1008313
Posted Thursday, October 21, 2010 6:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1008398
Posted Thursday, October 21, 2010 6:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 258, 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
Post #1008414
Posted Thursday, October 21, 2010 9:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1008928
Posted Monday, February 7, 2011 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 9, 2011 12:10 PM
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?


Post #1059759
Posted Tuesday, February 8, 2011 12:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1060057
Posted Tuesday, February 8, 2011 9:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:17 PM
Points: 1,651, Visits: 4,709
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.
Post #1060375
Posted Tuesday, February 8, 2011 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 9, 2011 12:10 PM
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.
Post #1060731
Posted Wednesday, February 9, 2011 7:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:17 PM
Points: 1,651, Visits: 4,709
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.

Post #1061128
Posted Saturday, May 18, 2013 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 2:38 PM
Points: 4, Visits: 50
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.


Great question, by the way! It's sad that we had to wait until SQL Server 2012 to get CREATE SEQUENCE...
Post #1454252
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse