Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tip: Resetting Identity Fields

By Banyardi Schmardi,

I have been cleaning up some existing code scripts and making them into standardized stored procedures.  Two of my scripts deal with backing out ETL data from raw and temp tables that was transformed incorrectly during an ETL routine.  Part of the code I use will reseed the identity field for the raw and temp tables to eliminate discontinuities in the identity field.  The original code was using a construct such as:

Declare @MaxIdentityValue BigInt

Set @MaxIdentityValue = (Select Top 1 IdentityField
                          From MyTable
                          Order By IdentityField Desc
                         )

DBCC CHECKIDENT(MyTable, RESEED, @MaxIdentityValue)

This could have been better served by

Declare @MaxIdentityValue BigInt

Set @MaxIdentityValue = (Select Max(IdentityField) 
                          From MyTable
                         )

DBCC CHECKIDENT(MyTable, RESEED, @MaxIdentityValue)

But I took some time and dove into what BOL had to say about DBCC CHECKIDENT and discovered this passage:

When the current identity value is larger than the maximum value in the table, then:

Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command.

or

Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.

Using this tip, I ran the following test:

If Exists(Select Name
           From sys.objects 
           Where name = 'TestReseed' 
           And type = 'U'
          )
 Drop Table TestReseed
GO
Create Table TestReseed(Fld1 Int Identity(1,1), Fld2 Int)
GO

Declare @ReseedValue Int = 0

While @ReseedValue < 10
 BEGIN
  Set @ReseedValue = @ReseedValue + 1
  Insert Into TestReseed(Fld2)
   Values(@ReseedValue)
 END

Select * From TestReseed

/* Results
Fld1 Fld2
1    1
2    2
3    3
4    4
5    5
6    6
7    7
8    8
9    9
10   10
*/

The identity field matches our control field.  Now let's delete 5 rows:

Delete From TestReseed Where Fld1 > 5

Select * From TestReseed

/* Results
Fld1 Fld2
1    1
2    2
3    3
4    4
5    5
*/

Now let's add in 5 rows again:

Declare @ReseedValue Int = 0

While @ReseedValue < 5
 BEGIN
  Set @ReseedValue = @ReseedValue + 1
  Insert Into TestReseed(Fld2)
   Values(@ReseedValue)
 END
 
/* Results
Fld1 Fld2
1    1
2    2
3    3
4    4
5    5
11   1
12   2
13   3
14   4
15   5
*/

Notice that the identity field now has a discontinuity in it between 5 and 11.  Now take out the top 5 rows again.

  Delete From TestReseed Where Fld1 > 5

At this point we can reset the identity column to where we want it with the following:


DBCC CHECKIDENT("TestReseed",RESEED,1)
DBCC CHECKIDENT("TestReseed",RESEED)

/* Results

Checking identity information: current identity value '20', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '1', current column value '5'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  */

Now we add back in our 5 rows:

Declare @ReseedValue Int = 0
While @ReseedValue < 5
 BEGIN
  Set @ReseedValue = @ReseedValue + 1
  Insert Into TestReseed(Fld2)
    Values(@ReseedValue)
 END

Select * From TestReseed

/* Results
1  1
2  2
3  3
4  4
5  5
6  1
7  2
8  3
9  4
10 5
*/

We see that with two simple lines of code, the identity value is set to the next contiguous value without having to determine the reseed value in a parameter.

Total article views: 8378 | Views in the last 30 days: 6
 
Related Articles
FORUM

DBCC CHECKIDENT performance problems

Dreadful performance trying to run DBCC Checkident

SCRIPT

Script to check current identity of Tables

Used to check all current identities on all user tables

FORUM

discepancy between dbcc checkident and select IDENT_CURRENT

Hi there I am getting different results between DBCC CHECKIDENT and select IDENT_CURRENT(). He...

BLOG

A Surprising Result from Ident_Current

If you haven’t used it (and I rarely do myself), Ident_Current (‘tablename’) returns the last identi...

FORUM

IDENTITY problem on Primary Key column in a table

IDENT_CURRENT('TableName') does not return the highest value in an IDENTITY Column

Tags
dbcc checkident    
identity property    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones