I have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER(), Aggregate functions in sql server and Change default date format in sql server.  Today I am going to explain regarding DBCC CHECKIDENT in SQL Server.

DBCC CHECKIDENT in SQL Server is used to check the identity value for the specified table and if needed correct the identity value.  Lets go through the detailed description on this.  Suppose I have a table with millions of records. I want to know the current identity value of the column. With the help of the below query i can know the current identity value of the table. Note that this query is not going to change the current identity value nor it is affecting the current table behavior.

Lets  take simple example and try to understand, how exactly DBCC CHECKIDENT in SQL Server will behave. Lets take example, we have table named with ‘StudentInfo’, which is having records as per below snapshot:

Query-1 : DBCC CHECKIDENT (‘StudentInfo‘)

This will return current identity value as 5 and current/last column in table which is 5.

 

Query-2: DBCC CHECKIDENT (‘StudentInfo’, NORESEED)

This will return current identity value as 5 and current/last column in table which is 5, same as first query, the only difference is ‘Specifies that the current identity value should not be changed’

Now suppose i have a case where my current identity value for a table is different than the maximum identity value stored in the column. To reset the identity column with the maximum value in the identity column I have two options. Let say I will deleted some of records and try to find identity of ‘StudentInfo’.

If I will add the new record in ‘StudentInfo’ table.  You can notice that newly added record has added with Identity 6 instead of 4.

If I want to add record with Identity with 4. I have to enable reseed on ‘StudentInfo’. To enable reseed, run below query.

DBCC CHECKIDENT (‘StudentInfo’, RESEED)

With the help of the below query we can set the identity column value to the specific supplied value. The “new_reseed_value” is the number or value i want to set my identity value.

DBCC CHECKIDENT (‘table_name‘, RESEED, new_reseed_value)

Example : DBCC CHECKIDENT (‘StudentInfo’, RESEED, 3), which will allow us to set us identity column and we can add new record with identity with 4 shown as below:

Thanks folks for reading this article, please don’t forgot to share and comments.  🙂