Reset the identity column value in MSSQL
Table of contents
How to reset the identity column value in SQL server?
To reset the current identity value for the table you should use DBCC CHECKIDENT function. It's a great way to control numbers sequence for the identity column.
DBCC CHECKIDENT function is very useful for database maintenance and can be applied to numeric types:
Why should use DBCC CHECKIDENT function and reset an Identity Column?
In most cases we want to set a new identity value, when:
DBCC CHECKIDENT Syntax
The DBCC CHECKIDENT function syntax looks as follows (MS docs):
DBCC CHECKIDENT
(
table_name
[, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
DBCC CHECKIDENT Arguments
For more details - check the official MS documentation:
Quick Example in 7 steps
A quick action plan looks as follows:
In the first step let's create the dummy table:
CREATE TABLE [Logs] (
[LogId] INTEGER NOT NULL IDENTITY(1, 1),
[Text] VARCHAR(MAX) NULL,
[Severity] VARCHAR(7) NULL,
[Created] DATETIME,
PRIMARY KEY ([LogId])
);
Next, let's insert 10 records:
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 1','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 2','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 3','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 4','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 5','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 6','Error',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 7','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 8','Warning',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 9','Info',GETDATE())
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 10','Info',GETDATE())
and display the data:
Now, the identity value should be 10.
Let's check the current identity value this using the NORESEED argument :
DBCC CHECKIDENT (Logs, NORESEED);
At this point all is good.
Now, Let's delete all data from the Logs table using the DELETE function:
DELETE FROM dbo.Logs;
Now, to reset the value to the initial point (let' pick 1), just execute the following snippet:
DECLARE @table_name VARCHAR(100) = 'Logs';
DECLARE @value INT = 1;
DBCC CHECKIDENT (@table_name, RESEED, @value)
Then we can insert a fresh record and execute the select command to see rows in the table.
INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 1','Error',GETDATE())
Bonus Tip: When you use the TRUNCATE TABLE command to clear your table, with a given example table (Logs) - the column identity value will be automatically reset to 1.
Conclusion
Resetting column identity value by DBCC CHECKIDENT function is super easy.
As we can see in the example - we've set column identity value back to the initial point and we can start populating it from the beginning.