Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Managing and maintaining SQL Server identity values


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > Identities

"SQL Server in The Cloud" - click to register for this free webcast


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 Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools