Sometimes, we may require to reseed identity column of a table due to various reason.
For example we have deleted a record from a Student which have an identity column StudentId. Below is schema
Now it has 3 rows as shown below
Now suppose we have deleted record 3 which is studentID 3.
Now when we want to insert a new record StudentId start with 4 and we want it should be 3.
So how to check and reseed it ?
To achieve this task SQL SERVER provided DBCC CHECKIDENT facility.
DBCC CHECKIDENT has 2 commands
1) To Check current Identity value.
DBCC CHECKIDENT (TABLENAME, NORESEED)
2) To reseed an identity column we have following command
DBCC CHECKIDENT ( TABLENAME , RESEED, VALUE)
Lets check student table identity to check current column value and current Identity value we will write following command
See below snap when after running this command
Now if you see 3 is current identity and column value 2 it means if I Insert a new row in tblStudentSource then that row will have studentId = 4 as a next identity column
Now I need to reseed it to 3 means if Insert a new record then studentId should be 3 to do this I have to reseed tblStudentSource.
Now to cross check whether it is reseed or not I write NORESEED command again as get following result
It means identity value 2 and current column value is 2 means if now I insert a new record we will get studentId = 3 as a identity column this what we want actually.
So. We did NORESSED, RESSED an identity column.
I hope it may help you somewhere in your work.