I wrote this post to help SQL lovers get informed on how to use.
This post will primarily inform the exact behavior when we RESEED.
When you reseed upon
a. Right after CREATE TABLE and no records have been inserted
b. Right after TRUNCATE TABLE.
c. Right after DELETE TABLE but no records have been inserted(Yes, why would someone do this?)
THE NEW IDENTITY VALUE WILL FOLLOW THE RESEED VALUE.
But, when you reseed after records have been inserted. The followup new identity value will be an increment of 1 of the reseed value.
Code below show the different scenarios:
DECLARE @ReseedValue INT = 100;
IF OBJECT_ID(N’dbo.SimpleTable’, N’U’) IS NOT NULL
DROP TABLE dbo.SimpleTable;
CREATE TABLE dbo.SimpleTable
(
ID INT NOT NULL IDENTITY(1, 1),
[TestCol] CHAR NULL
);
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);
INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);
SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
100 A
101 B
102 C
*/
TRUNCATE TABLE dbo.SimpleTable;
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);
INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);
SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
100 A
101 B
102 C
*/
DELETE dbo.SimpleTable;
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);
INSERT INTO dbo.SimpleTable VALUES
(‘A’)
, (‘B’)
, (‘C’);
SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
101 A
102 B
103 C
*/
SET @ReseedValue = 200;
DBCC CHECKIDENT(N’dbo.SimpleTable’, RESEED, @ReseedValue);
INSERT INTO dbo.SimpleTable VALUES
(‘D’);
SELECT * FROM dbo.SimpleTable;
/*
Result
ID TestCol
101 A
102 B
103 C
201 D
*/
Happy coding!