auto increment - Reset AutoIncrement in SQL Server after Delete

ID : 10274

viewed : 33

Tags : sql-serverauto-incrementdelete-rowsql-server

Top 5 Answer for auto increment - Reset AutoIncrement in SQL Server after Delete

vote vote

96

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.

vote vote

82

DBCC CHECKIDENT('databasename.dbo.tablename', RESEED, number) 

if number=0 then in the next insert the auto increment field will contain value 1

if number=101 then in the next insert the auto increment field will contain value 102


Some additional info... May be useful to you

Before giving auto increment number in above query, you have to make sure your existing table's auto increment column contain values less that number.

To get the maximum value of a column(column_name) from a table(table1), you can use following query

 SELECT MAX(column_name) FROM table1 
vote vote

75

semi idiot-proof:

declare @max int;   select @max = max(key) from table;   dbcc checkident(table,reseed,@max) 

http://sqlserverplanet.com/tsql/using-dbcc-checkident-to-reseed-a-table-after-delete

vote vote

64

If you're using MySQL, try this:

ALTER TABLE tablename AUTO_INCREMENT = 1 
vote vote

55

I figured it out. It's:

 DBCC CHECKIDENT ('tablename', RESEED, newseed) 

Top 3 video Explaining auto increment - Reset AutoIncrement in SQL Server after Delete

Related QUESTION?