(last updated: 2019-01-31 @ 22:45 EST / 2019-02-01 @ 03:45 UTC )
Today’s “Question of the Day” on SQL Server Central, Cleaning up the Identity, is about using DBCC CHECKIDENT to reset the seed value of an IDENTITY column to a specific starting value. The question asked what the next Identity value would be after removing all rows in the table via the TRUNCATE TABLE statement. The “new seed value” is “0”, and the “increment value” is “1”. The correct answer is “0”, and the CHECKIDENT
documentation is in agreement with this behavior. However, I remember recently seeing a case where the next value (assuming the same initial values) was “1” (i.e. “new seed value” + “increment value”). I re-read the documentation and noticed that something was missing. Here is what it currently (as of 2019-01-31 12:45 PM ET / 17:45 PM UTC) states (slightly reduced, reorganized, and rephrased for clarity by me):
DBCC CHECKIDENT command | Identity correction or corrections made |
---|---|
DBCC CHECKIDENT ( table_name [, RESEED] ) | * If the current identity value is less than the maximum value stored in the identity column, it is reset using the maximum value in the identity column. * If the current identity value is larger than the maximum value in the table, or all rows are deleted from the table, it is not reset. |
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) | Current identity value is set to the new_reseed_value.
|
So, when specifying a “new_reseed_value“, the possible scenarios covered are:
- Rows exist
- No rows due to none inserted since the table was created
- No rows due to
TRUNCATE TABLE
operation
What’s missing? The following scenario:
No rows due to DELETE
operation!!
And because of this omission, people interpret the documentation to be saying that “no rows”, for any reason, results in the same behavior: “the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity”. Well, is this really what happens?
It is easy enough to test this. And while we are at it, why not test all of the scenarios because there could be other mistakes in the documentation, right?
TESTS
SETUP
IF (OBJECT_ID(N'tempdb..#ReseedTest') IS NOT NULL) BEGIN DROP TABLE #ReseedTest; END; -- Use an increment of anything but "1" to make it easier -- to see if a RESEED uses "new_reseed_value" + 1 CREATE TABLE #ReseedTest ( [ID] INT IDENTITY(3, 2) NOT NULL, [Name] VARCHAR(30) ); GO
TEST 1: Empty table (no rows added since table was created)
DBCC CHECKIDENT(N'#ReseedTest', RESEED, 15); -- Checking identity information: current identity value 'NULL'. INSERT #ReseedTest ([Name]) VALUES ('Ian'); SELECT * FROM #ReseedTest; /* ID Name 15 Ian */ -- next value = "new_reseed_value"
TEST 2: DELETE
DELETE FROM #ReseedTest; DBCC CHECKIDENT('#ReseedTest', RESEED, 5); -- Checking identity information: current identity value '15'. INSERT #ReseedTest ([Name]) VALUES ('Bernard'); INSERT #ReseedTest ([Name]) VALUES ('Peter'); INSERT #ReseedTest ([Name]) VALUES ('Stephen'); SELECT * FROM #ReseedTest; /* ID Name 7 Bernard 9 Peter 11 Stephen */ -- next value = "new_reseed_value" + "current increment"
TEST 3: Non-empty table (new value not below current max value)
DBCC CHECKIDENT(N'#ReseedTest', RESEED, 20); -- Checking identity information: current identity value '11'. INSERT #ReseedTest ([Name]) VALUES ('Ian'); SELECT * FROM #ReseedTest; /* ID Name 7 Bernard 9 Peter 11 Stephen 22 Ian */ -- next value = "new_reseed_value" + "current increment"
TEST 4: TRUNCATE TABLE
TRUNCATE TABLE #ReseedTest; DBCC CHECKIDENT('#ReseedTest', RESEED, 10); -- Checking identity information: current identity value 'NULL'. INSERT #ReseedTest ([Name]) VALUES ('Bernard'); INSERT #ReseedTest ([Name]) VALUES ('Peter'); INSERT #ReseedTest ([Name]) VALUES ('Stephen'); SELECT * FROM #ReseedTest; /* ID Name 10 Bernard 12 Peter 14 Stephen */ -- next value = "new_reseed_value"
TEST 5: Non-empty table (new value below current max value)
DBCC CHECKIDENT(N'#ReseedTest', RESEED, 12); -- Checking identity information: current identity value '14'. INSERT #ReseedTest ([Name]) VALUES ('Gillian'); SELECT * FROM #ReseedTest; /* ID Name 10 Bernard 12 Peter 14 Stephen 14 Gillian */ -- next value = "new_reseed_value" + "current increment"
TEST 6: DELETE and no “new_reseed_value” specified
DELETE FROM #ReseedTest WHERE [ID] > 10; DBCC CHECKIDENT('#ReseedTest', RESEED); -- Checking identity information: current identity value '14', -- current column value '10'. INSERT #ReseedTest ([Name]) VALUES ('Stephen'); SELECT * FROM #ReseedTest; /* ID Name 10 Bernard 16 Stephen */ -- NO RESET!! -- next value = "current identity value" + "current increment" -- (normal behavior)
TEST 7: no “new_reseed_value” specified, but “current identity” < max value in column
DBCC CHECKIDENT('#ReseedTest', RESEED, 10); -- Checking identity information: current identity value '16'. INSERT #ReseedTest ([Name]) VALUES ('Peter'); SELECT * FROM #ReseedTest; /* ID Name 10 Bernard 16 Stephen 12 Peter */ -- next value = "new_reseed_value" + "current increment" (same as Test 5) DBCC CHECKIDENT('#ReseedTest', RESEED); -- Checking identity information: current identity value '12', -- current column value '16'. INSERT #ReseedTest ([Name]) VALUES ('Ian'); SELECT * FROM #ReseedTest; /* ID Name 10 Bernard 16 Stephen 12 Peter 18 Ian */ -- next value = "max column value" + "current increment"
TEST 8: TRUNCATE TABLE and no “new_reseed_value” specified
TRUNCATE TABLE #ReseedTest; DBCC CHECKIDENT('#ReseedTest', RESEED); -- Checking identity information: current identity value 'NULL', -- current column value 'NULL'. INSERT #ReseedTest ([Name]) VALUES ('Ian'); SELECT * FROM #ReseedTest; /* ID Name 3 Ian */ -- next value = "initial seed value"
CONCLUSION
There are two things that are either missing or incorrect in the documentation:
- When there were no rows due to a
DELETE
operation, the next assigned value is actually “new_reseed_value” + “current increment” - I tested on SQL Server versions 2005 SP4, 2008 R2, 2012 SP4, 2017 CU12, and 2019 CTP 2.2, and there is no difference in behavior, not even when rows are present.
I will submit an update for the documentation to make these corrections (there’s also a typo in the examples section).
UPDATE 2019-01-31
Documentation corrections have been submitted via PR #1602.
[…] Solomon Rutzky covers the four primary scenarios when running DBCC CHECKIDENT and specifying a new reseed value: […]
I got this “wrong” even though I done something with a table recently where I had to excise the rows. I remembered that I’d certainly reseeded it to the max value of the table. When the new rows were reentered, the first value was the seed plus the increment. So I was sure when I answered it I’d get a correct. I did not. Thanks for digging in and helping us all to realize that there was more to the question that first meets the eye.
To state your experiences another way: if there are no rows in a table, RESEED without a new seed value will not work as expected, because it needs to have a record in order to find a maximum value. If you want the seed reset in this scenario, you must specify the new seed value to be certain you are getting what you want.
I would file this under the heading “SQL Server Bugs,” because the expected max value when no records exist should be zero. However, documentation explicitly calling attention to this unexpected result would be helpful, even though it’s counter-intuitive.
Thanks for making me aware of it. It’s bitten me before and now I know why.
Thanks for the clear explanation. I found that I didn’t know what would happen if you used a reseed that resulted in a value already in that column – in Test 5 “The Other Two” end up with the same identity value. I now know that’s because it’s not got a unique constraint, and if the identity column does have that constraint then it throws an error and doesn’t enter Gillian’s record. I’d assumed that it would use the next available identity and that it would be unique by default. I’ll look out for that!