SQL Clone
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86508 Visits: 41098
wmcgeorge (5/18/2013)
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...


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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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