UmbraCare LOGO

Resetting Identity Column Values in SQL Server using DBCC CHECKIDENT

Step-by-step guide on how to use the DBCC CHECKIDENT function in SQL Server to reset identity column values. Understand why and when to reset identity columns and gain insights from practical examples

Published on: , by:
Piotr Bach
Piotr Bach
Wojciech Tengler
Wojciech Tengler
MS SQL

Table of contents

Introduction to DBCC CHECKIDENT in SQL Server

Resetting identity column values in SQL Server requires an in-depth understanding of the DBCC CHECKIDENT function.

This built-in command provides a straightforward way to manage sequence numbers for identity columns. 

Suitable for several numeric types, it's an invaluable tool for database maintenance.

Supported numeric types:

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

Why Consider Resetting Identity Column Values?

Identity columns provide automatic numbering in database tables.

However, there are scenarios where adjusting the sequence becomes necessary:

  • Managing custom numbering sequences.
  • Reordering after table rows have been deleted.
  • Starting fresh after archiving table rows.
  • Rectifying an incorrectly set identity seed.

Exploring DBCC CHECKIDENT: Syntax and Arguments

Microsoft provides a comprehensive guide on the DBCC CHECKIDENT function syntax:

DBCC CHECKIDENT
 (
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]

Understanding the arguments:

  • new_reseed_value: The desired highest identity value for the table.
  • RESEED: Command to change the current identity value.
  • NORESEED: Returns the table's current identity value.

For deeper insights, consider visiting the official MS documentation.

A Hands-On Example: Resetting the Identity Column (7 steps)

To better grasp this concept, let's walk through a comprehensive example involving the creation, manipulation, and resetting of an identity column in the dbo.Logs table.

A quick action plan looks as follows:

  • Create table dbo.Logs with an integer identity column
  • Insert ten 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: 

Slect query from logs table

Now, the identity value should be 10. 

Let’s check the current identity value using the NORESEED argument :

SQL checking identity value

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())
SQL Select from Logs table 2
SQL Check Ident

Bonus: Identity Reset with TRUNCATE TABLE Command

A noteworthy mention is the TRUNCATE TABLE command.

When used to clear your table, for instance, our example table (Logs), the identity column value automatically resets to its starting point.

Key Takeaways: Simplifying Identity Resets with DBCC CHECKIDENT

The ability to reset column identity values with the DBCC CHECKIDENT function is a straightforward yet invaluable process.

Through our hands-on example, we've demonstrated the simplicity of setting the identity value back to its inception.

This ensures data consistency and a structured approach to database management.

Wrapping Up: Mastering Identity Resets in SQL

🛠️ As database administrators or developers, mastering tools like DBCC CHECKIDENT can significantly streamline our tasks and optimize database structures. 📊

✨ We hope this guide has illuminated the intricacies of resetting identity columns in SQL Server. 📘

🤝 If you're seeking expert assistance, please get in touch with us.

📚 For more insights and guides, explore our blog. 🔍

Related articles

↑ Top ↑