I recently had cause to reseed an identity column on a table in SQL Server, which got me wondering “If we reseed an identity column to a value lower than the existing identity values in the column, could it cause a clash?” Because why wouldn’t you wonder if you can cause something to break, whilst performing a fairly simple task!?
Let’s find out what happens…
Setup the Test
First let’s set up a test table within a new database:
CREATE DATABASE IdentityDB;
GO
USE IdentityDB;
GO
CREATE TABLE dbo.SomeStuff
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Something NVARCHAR(50)
);
We’ve created a table with an integer identity column as its primary key, so all values in that column must be unique.
Let’s populate with 1000 GUID values…
INSERT INTO dbo.SomeStuff
SELECT TOP 1000
NEWID()
FROM sys.messages;
… and verify that’s what we see:
SELECT * FROM dbo.SomeStuff;
Right on!
Run the Test
Now we’ve set up our test environment, let’s run our test.
Let’s delete the first 100 rows in the table:
DELETE FROM dbo.SomeStuff WHERE Id <= 100;
Now we can see the lowest IDs:
SELECT * FROM dbo.SomeStuff ORDER BY Id;
As expected, they are all now over 100:
Now we’ll reseed the identity of the column back to 0:
DBCC CHECKIDENT ('dbo.SomeStuff', RESEED, 0);
We get the following output in the messages tab:
Checking identity information: current identity value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The command above resets the current identity value to 0 and therefore the next identity value assigned will be 1 (because the increment specified on our column was 1). The output displays the current identity value before making the change.
At this point then, let’s try adding 101 values – the first 100 will get IDs 1 – 100 but what about the 101st value? Will it get ID 1001 (the first available)? or will it get ID 101 (the next value) and what will happen if it does, given we have a value with ID 101?
Instead of GUIDs, this time I’m just inserting plain old integers to make it obvious to the eye which are the rows we’ve inserted here and which are the rows that were already in the table:
INSERT INTO dbo.SomeStuff
SELECT TOP 101
ROW_NUMBER() OVER (ORDER BY message_id)
FROM sys.messages;
What happens to record 101?
Drum roll…
Msg 2627, Level 14, State 1, Line 31
Violation of PRIMARY KEY constraint 'PK__SomeStuf__3214EC073CD5B184'. Cannot insert duplicate key in object 'dbo.SomeStuff'. The duplicate key value is (101).
The statement has been terminated.
Identity clash – row 101 tried to use ID 101 which already exists, and since we have a primary key, that is a violation and so an error is thrown, the transaction rolls back, and nothing is inserted at all.
As an aside, the identity values are removed from the pool despite the rollback, so if we try to insert a single row now, we get an error as it is trying to insert ID 101:
INSERT INTO dbo.SomeStuff VALUES ('What is my identity?');
Msg 2627, Level 14, State 1, Line 31
Violation of PRIMARY KEY constraint 'PK__SomeStuf__3214EC073CD5B184'. Cannot insert duplicate key in object 'dbo.SomeStuff'. The duplicate key value is (101).
The statement has been terminated.
We can check the current identity value as follows:
DBCC CHECKIDENT ('dbo.SomeStuff', NORESEED);
Checking identity information: current identity value '102', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
We can see the current identity value is 102 as our first insert tried to insert 101 records and rolled back, followed by our single row insert which also rolled back.The maximum value of the column is 1000, so we have to reseed to 1000 to be able to insert records. If we do so:
DBCC CHECKIDENT ('dbo.SomeStuff', RESEED, 1000);
Checking identity information: current identity value '102'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now let’s try an insert again, I’ll return the SCOPE_IDENTITY() which returns the last ID inserted into an IDENTITY column in the current scope:
INSERT INTO dbo.SomeStuff VALUES ('What is my identity?');
SELECT SCOPE_IDENTITY();
Our result confirms we can now insert records again:
Conclusion
We have found that reseeding the identity value of a column can result in identity clashes, so be sure to reseed to a value above the current maximum identity value of the column.