Reset the identity column value in MSSQL

How to reset the identity column value in the SQL server?

To reset the current identity value for the table you should use DBCC CHECKIDENT function. It’s a great way to control the numbers sequence for the identity column.

DBCC CHECKIDENT function is very useful for database maintenance and can be applied to numeric types:

• smallint
• bigint
• tinyint
• numeric
• integer
• decimal

Why should use DBCC CHECKIDENT function and reset an Identity Column?

In most cases we want to set a new identity value, when:

• Numbers sequence should be controlled in general
• Table rows were deleted and numbering should start from the beginning
• Table rows were archived and custom numbering should be set from the initial point
• The identity seed was incorrectly and the issue should be fixed

Learn 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 ]

Learn DBCC CHECKIDENT Arguments

• new_reseed_value – highest identify value in the table
• RESEED – forces current identity value to change
• NORESEED – returns the current value

For more details – check the official MS documentation:

Quick Example in 7 simple steps

A quick action plan looks as follows:

• Create table dbo.Logs with an integer identity column
• Insert 10 rows
• Check current identity value
• Delete records from the table
• Reset identity value to 1
• Insert 1 record
• Check identity value – should be equal to 1

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 :

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.

Final thoughts

Resetting column identity value by DBCC CHECKIDENT function is super easy. 

As we can see in the example –  we’ve set the column identity value back to the initial point and we can start populating it from the beginning.