When using SQL Server as your database, you usually use an identity column as your primary key to a table. My current assignment that I am working on has several lookup tables that hold key pieces of information based on a calendar year. Each year these values are loaded into the table. Even though the year is a column in the table, the client wanted to load new data for the upcoming year. I was asked to offset whatever the current identity value is and replace this value to distinguish the difference in data.

The first check I made was to establish the current identity value. To do this I used the following command:

This produced a result of:

Checking identity information: current identity value ‘3187’, current column value ‘3187’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As stated by Microsoft, the signature to DBCC CHECKIDENT, NORESEED is defined as:
NORESEED – Specifies that the current identity value should not be changed.

After a team discussion, we decided to start the new identity at the next 1000 mark interval. So in order to accomplish this task I used the following command:

This produced a result of:

Checking identity information: current identity value ‘3187’, current column value ‘3999’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As stated by Microsoft, the signature to DBCC CHECKIDENT, RESEED is defined as:
RESEED – Specifies that the current identity value should be changed.

Please note, whatever value you reseed to, the true next inserted item will be the current value + 1. So in our case it would be 4000.

Task completed.

 

Like this post? Share it!