dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 14, 2019

How to Reseed Your Identity Column Value in SQL Server

By Greg Larsen

Have you deleted a bunch of rows from a table that has an identity column value? Do you now find that when you insert a new row, you find that the identity column value was set to one higher than the highest identity column value that your table original had? This can happen if you make a mistake and inserted a bunch of rows by accident, and then you delete those accidentally insert rows without reseeding your identity value.

If you have made this insert mistake or just delete a bunch of rows at the high end of the identify column value range, then you might want to reseed the identify column value, prior to adding any more rows. If you don't reseed your identity value, then you might find that you have large gaps in your identify column values.

In order to reseed the identity column values, you can run this command:

DBCC CHECKIDENT ('youtable', RESEED, <new seed value>)

Where “<new seed value>” is set to the highest identify value in your table.  

After running the DBCC command your next inserted row into your table will be inserted with an identity column value 1 higher than the new seed value specified in the DBCC command.

You must use this command with care. Do not set the new seed value to be less than the highest identity column value in the table. If you set your new seed value to be less than the highest identity value in your table, then you might end up with SQL Server trying to insert a new identity value that has the same value of as an identity value already in your table. When this happens your insert statement will fail; therefore, be careful when you reseed the identity value on your tables.

# # #

See All Articles by Columnist Gregory A. Larsen



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM