SQL with Manoj

In my [previous post] I discussed about IDENTITY property with a [demo video].

Here in this post we will see how you can Re-Seed a particular IDENTITY Column value.
 

There are times when you want to Re-Seed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.
 

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reSeed, NewseedValue);

-- Example:
DBCC CHECKIDENT ('Person.Contact', reseed, 100);

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100, otherwise you might duplicates.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.
 

–> You can check the same demo here:

–> Sample SQL Code:

-- Check current IDENTITY value, and re-seeds the value with largest value of the column:
DBCC CHECKIDENT ('dbo.Employee'); -- do not use it.
GO

-- Re-seed IDENTITY column value:
DBCC CHECKIDENT ('dbo.Employee', reseed, 102);
GO

-- Check current IDENTITY value (without Re-seeding):
DBCC CHECKIDENT ('dbo.Employee', noreseed);
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Deepak B')

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Manish A')
GO

SELECT * FROM [dbo].[Employee]
GO