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())
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.
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];
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.