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!