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.

4 thoughts on “How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)?”

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

    Like

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

    Like

  3. 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!

    Liked by 1 person

Leave a Reply to Ed Eaglehouse Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s