DBCC CHECKIDENT in SQL Server(TSQL)

 

The following operations can be performed with the DBCC CHECKIDENT command.

  • The current identitiy value of an identity column can be found.
  • The maximum identitiy value of an identitiy column can be found.
  • The current identitiy value of an identity column can be set as any number.
  • The current identitiy value of an identitiy column can be set as the maximum identitiy value.

First, we create a table with the help of the following script and add a few records into this table.

Find current and maximum identitiy value of an identitiy column:

Set an identity column’s current identity value to any number:

Set the Current Identitiy value as a lower number:

Let’s check the current and maximum identitiy again:

As you can see, Current identity value is 10 while maximum identity value is 12.

To set the current identity value of an identity column as the maximum identitiy value:

If the current identity value is smaller than the maximum identitiy value, we can set the current identitiy value as the maximum identity value as follows.

Check the current and maximum identitiy value again:

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *