In this article, we will learn how to RESEED an IDENTITY column value in SQL Server. SQL Server IDENTITY column generates sequential values for new records inserted in the table using a SEED value. This feature is very useful when you want to reset the identity column. The DBCC CHECKIDENT management command is used to reset the identity counter.
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ]
Example 1: In this example, we will see how to RESEED an IDENTITY column value if DELETE all the records from the table.
DBCC CHECKIDENT ('Your Table Name', RESEED, 0);Where 0 is the New Reseed value.
Example 2: In this example, we will learn how to RESEED an IDENTITY Column value When you want to delete test rows and restore the value to the previous value, you do the following.
-- Delete Record From table
DELETE FROM tbl_emp
WHERE id >= 4 ;
DECLARE @NewSeed NUMERIC(10)
-- Get the Last Value from the Table from where
-- You want to restore the Identity Column Value
SELECT @NewSeed = MAX(id)
FROM tbl_emp ;
-- RESEED the IDENTITY column Value
DBCC CHECKIDENT (tbl_emp, RESEED, @NewSeed)
Where tbl_emp replace with your table name and,
Replace id Column with your identity Column
I hope this article will help you to understand how to RESEED an IDENTITY column value in SQL Server.
Share your valuable feedback, please post your comment at the bottom of this article. Thank you!
Comments