UmbraCare LOGO

Resetting Identity Column Values in SQL Server using DBCC CHECKIDENT

Learn how to use the DBCC CHECKIDENT function in SQL Server to reset identity column values. Understand why and when to reset identity with practical example

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

What is DBCC CHECKIDENT in SQL Server?

The DBCC CHECKIDENT command in SQL Server is used to check and reset identity column values in a table.

It allows you to ensure sequence consistency in a table, particularly after modifications like deletions or reseeding.

Supported numeric types include:

  • smallint

  • bigint

  • tinyint

  • numeric

  • integer

  • decimal

This guide walks you through the scenarios, syntax, and step-by-step examples.

Why Should You Reset Identity Column Values?

Identity columns automatically number rows in a table, but there are times when you’ll need to adjust these values.

Here’s when resetting identity columns can help:

  • Archived Data: If you’ve archived some data and deleted those rows, resetting the identity ensures new rows start with a clean numbering sequence.

  • Testing: When setting up mock data, you may need to reseed identities to align with test scenarios.

  • Cleanup: After removing all rows in a table, reset the identity to ensure new rows start at the desired number.

  • Correction: Fix an identity seed that was incorrectly set, avoiding overlap or gaps in numbering.

  • Migration: When importing or exporting data, reset identities to maintain consistent numbering across systems.

Syntax and Arguments of DBCC CHECKIDENT

The basic syntax for DBCC CHECKIDENT is as follows:

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

Key arguments:

  • table_name: The name of the table containing the identity column.

  • new_reseed_value: The desired identity value to set.

  • RESEED: Resets the current identity value.

  • NORESEED: Returns the current identity value without changing it.

Step-by-Step Example: Resetting the Identity Column

Let’s explore a practical example of resetting the identity column for the dbo.Logs table.

This example includes creating a table, inserting data, resetting the identity, and validating the result.

Step 1: Create the Table

CREATE TABLE [Logs] (
    [LogId] INTEGER NOT NULL IDENTITY(1, 1), 
    [Text] VARCHAR(MAX) NULL,
    [Severity] VARCHAR(7) NULL,
    [Created] DATETIME,
    PRIMARY KEY ([LogId])
);

Step 2: Insert Data

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())
Slect query from logs table

Step 3: Check Current Identity Value

DBCC CHECKIDENT ('Logs', NORESEED);

This command returns the current identity value.

For the example above, it will display 10.

SQL checking identity value

Step 4: Delete Data

DELETE FROM dbo.Logs;

Step 5: Reset Identity Value

To reset the identity to 1:

DBCC CHECKIDENT ('Logs', RESEED, 0);

Step 6: Insert a New Record

INSERT INTO [Logs]([Text],[Severity],[Created]) VALUES('Lorem ipsum 1','Error',GETDATE())

Step 7: Verify the Reset

SELECT * FROM [Logs];
SQL Select from Logs table 2
SQL Check Ident

Resetting Identity with TRUNCATE TABLE

The TRUNCATE TABLE command removes all rows from a table and automatically resets the identity seed to its initial value.

For example, consider the table dbo.Logs, which initially contain 10 records:

-- Verify the initial number of records in the table
SELECT COUNT(*) AS RecordCount FROM dbo.Logs;
-- Output: RecordCount: 10

-- Check the current identity value
DBCC CHECKIDENT ('Logs', NORESEED);
-- Output: Current identity value: 10

-- Truncate the table to remove all records and reset identity
TRUNCATE TABLE dbo.Logs;

-- Check the identity value again
DBCC CHECKIDENT ('Logs', NORESEED);
-- Output: Current identity value: NULL

-- Insert a new record to set identity
INSERT INTO [Logs] ([Text], [Severity], [Created]) VALUES ('Test entry', 'Info', GETDATE());

-- Check identity value again
DBCC CHECKIDENT ('Logs', NORESEED);
-- Output: Current identity value: 1

-- Verify the number of records in the table
SELECT COUNT(*) AS RecordCount FROM dbo.Logs;
-- Output: RecordCount: 1

TRUNCATE is faster than DELETE because it does not log individual row deletions but instead logs the deallocation of data pages.

Key Takeaways

  • The DBCC CHECKIDENT command simplifies identity management for SQL Server tables.

  • Use DELETE followed by DBCC CHECKIDENT for precise reseeding or TRUNCATE TABLE for automatic resets.

  • Always verify the changes using SELECT queries to ensure proper reseeding.

  • For safety, consider wrapping these operations in a transaction to avoid partial changes in case of errors.

EDITOR'S PICK

↑ Top ↑