(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] ) |
|
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.