Managing and maintaining SQL Server identity values


By:   |   Updated: 2006-12-05   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Identities

Problem
One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table.  You have the ability to have one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each of the records in your table.  Once in awhile you may delete the data in the table and what to reset the value or maybe you just want to know what the next number will be.  So how do you figure this out?

Solution

There is a DBCC command that you can use to determine the current value of the identity value or you can use this to reset the value.  The command is:

DBCC CHECKIDENT

There are a few options that you can use for this command:

The first option will show you the current value of the identity column without affecting the value.

DBCC CHECKIDENT ('table_name', NORESEED)

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

This next option will reset the next identity value if it is needed.

DBCC CHECKIDENT ('table_name', RESEED)

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

This last option will reset the identity starting with the value that you provide.

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

Checking identity information: current identity value '14', current column value '30'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Next Steps
  • If you were not sure how to manage your identity values take a look at this command and how you can use it to see the value or reset the values if needed
  • Look for other great tips on www.mssqltips.com


Last Updated: 2006-12-05


get scripts

next tip button



About the author





Comments For This Article





download


Recommended Reading

Using Identity Insert to keep SQL Server table keys in sync

Properly Capturing Identity Values in a SQL Server Database

Auto generated SQL Server keys with the uniqueidentifier or IDENTITY

Populate a SQL Server column with a sequential number not using an identity

SQL Server 2012 Sequence Numbers





get free sql tips
agree to terms


Learn more about SQL Server tools