How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)?

(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. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. If rows are present in the table, the next row is inserted with the new_reseed_value value. In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value.

So, when specifying a “new_reseed_value“, the possible scenarios covered are:

1. Rows exist
2. No rows due to none inserted since the table was created
3. 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:

1. When there were no rows due to a DELETE operation, the next assigned value is actually “new_reseed_value” + “current increment”
2. 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.