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 Saturday, May 18, 2013 11:50 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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.


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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1454284
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse