Reset AutoIncrement in SQL Server after Delete

sql-server auto-increment delete-row
jumbojs picture jumbojs · Feb 4, 2009 · Viewed 378.6k times · Source

I've deleted some records from a table in a SQL Server database. Now the ID's go from 101 to 1200. I want to delete the records again, but I want the ID's to go back to 102. Is there a way to do this in SQL Server?

Answer

Robert Wagner picture Robert Wagner · Feb 4, 2009

Issue the following command to reseed mytable to start at 1:

DBCC CHECKIDENT (mytable, RESEED, 0)

Read about it in the Books on Line (BOL, SQL help). Also be careful that you don't have records higher than the seed you are setting.

Related questions

How do I add a auto_increment primary key in SQL Server database?

I have a table set up that currently has no primary key. All I need to do is add a primary key, no null, auto_increment. I'm working with a Microsoft SQL Server database. I understand that it can't be …

Read More
How to generate auto increment field in select query

For example I have a table with 2 columns, first_name and last_name with these values Ali Khani Elizabette Amini Britney Spears ,... I want to write a select query that generate a table like this: 1 Ali Khani 2 Elizabette Amini 3 Britney …

Read More
SQL Server, How to set auto increment after creating a table without data loss?

I have a table table1 in SQL server 2008 and it has records in it. I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table? I …

Read More