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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


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