October 21, 2010 at 4:59 am
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!
October 21, 2010 at 6:37 am
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
Change is inevitable... Change for the better is not.
October 21, 2010 at 6:54 am
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.
October 21, 2010 at 9:02 pm
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
Change is inevitable... Change for the better is not.
February 7, 2011 at 10:45 am
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?
February 8, 2011 at 12:51 am
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
Change is inevitable... Change for the better is not.
February 8, 2011 at 9:38 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 8, 2011 at 4:27 pm
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.
February 9, 2011 at 7:34 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 18, 2013 at 7:05 am
Quick word of caution: [font="Courier New"]TRUNCATE TABLE[/font] will reset [font="Courier New"]IDENTITY()[/font] 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 [font="Courier New"]CREATE SEQUENCE[/font]...
May 18, 2013 at 11:50 am
wmcgeorge (5/18/2013)
Quick word of caution: [font="Courier New"]TRUNCATE TABLE[/font] will reset [font="Courier New"]IDENTITY()[/font] 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 [font="Courier New"]CREATE SEQUENCE[/font]...
Heh... a better cautionary note would be to never ever use something that you don't know the ramifications of. It clearly states the following in Books Online under "Truncate Table".
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
Contrary to what that note states, it IS possible to take advantage of the minimal logging and guaranteed removal of all pages that TRUNCATE TABLE has over mere DELETE's provided you've met the conditions for TRUNCATE TABLE to run (also in Books Online, the "help" system that comes with SQL Server). Here's how...
First, we need a test table to demo with.
--=============================================================================
-- Create a test table
--=============================================================================
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL DROP TABLE #MyHead
;
--===== Create the empty test table
CREATE TABLE #MyHead
(
SomeID INT IDENTITY(1,1),
SomeData UNIQUEIDENTIFIER
)
;
And now, one possible solution. As always, the details of how it works are in the code. Run the following as may times as you want to see that the sequence of the IDENTITY column is preserved.
/******************************************************************************
Demonstrate how to truncate a table while preserving the continuous value
of the IDENTITY column. Run this as many times as you want.
******************************************************************************/
--=============================================================================
-- Truncate the table and preserve the IDENTITY seed so that it will
-- continue numbering from where it left off.
--=============================================================================
--===== First, show the current MIN and MAX values of the IDENTITY column
-- just so we can see that this all works correctly. This is NOT a part
-- of the solution. This will obviously return NULLs the first time
-- it's executed because the table is empty.
SELECT MinSomeID = MIN(SomeID),
MaxSomeID = MAX(SomeID)
FROM #MyHead
;
--===== Declare an obviously named variable
DECLARE @NewSeed INT
;
--===== Begin a transaction and get the new seed while locking the table to
-- prevent anyone from sneaking in on the process.
-- The ISNULL is to handle the table when it first comes into existance
-- which is not the case here but is included for completeness of the
-- solution.
BEGIN TRANSACTION
;
--===== Using ISNULL on IDENT_CURRENT doesn't seem to work on my machine
-- so we'll fix that in a minute. In the meantime, get the current
-- value of the IDENTITY. The TABLOCKX will cause the code to wait
-- until there's nothing using the table.
SELECT TOP 1
@NewSeed = IDENT_CURRENT('#MyHead')
FROM #MyHead WITH (TABLOCKX)
;
--===== Now, increment that value current IDENTITY value to be used as a RESEED
SELECT @NewSeed = ISNULL(@NewSeed,0)+1
;
--===== Empty the table. This will be much faster and cause nearly 0 logging
-- compared to doing a delete.
TRUNCATE TABLE #MyHead
;
--===== Since TRUNCATE reseeds the IDENTITY column with it's original value,
-- we need to reseed it with the expected continuation value.
DBCC CHECKIDENT ('#MyHead', RESEED, @NewSeed)
;
--===== Let other people use the table with the new continued seed.
COMMIT
;
--=============================================================================
-- Insert some data into the trucated table and note the we were able to
-- continue the sequence of the IDENTITY value.
--=============================================================================
--===== Insert the test data
INSERT INTO #MyHead
(SomeData)
SELECT TOP 100
SomeData = NEWID()
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Demonstrate that the sequence of the IDENTITY column continued.
-- This is NOT a part of the solution.
SELECT MinSomeID = MIN(SomeID),
MaxSomeID = MAX(SomeID)
FROM #MyHead
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply