Reset AutoIncrement in SQL Server after Delete

Question

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 1

Try this:

ALTER TABLE tablename AUTO_INCREMENT = 1

Answer 2

I figured it out. It's:

 DBCC CHECKIDENT ('tablename', RESEED, newseed)

Answer 3

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

Answer 4

Based on the accepted answer, for those who encountered a similar issue, with full schema qualification:

([MyDataBase].[MySchemaName].[MyTable])... results in an error, you need to be in the context of that DB

That is, the following will throw an error:

DBCC CHECKIDENT ([MyDataBase].[MySchemaName].[MyTable], RESEED, 0)

Enclose the fully-qualified table name with single quotes instead:

DBCC CHECKIDENT ('[MyDataBase].[MySchemaName].[MyTable]', RESEED, 0)

Answer 5

Delete and Reseed all the tables in a database.

    USE [DatabaseName]
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"       -- Disable All the constraints
    EXEC sp_MSForEachTable "DELETE FROM ?"    -- Delete All the Table data
    Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)' -- Reseed All the table to 0
    Exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"  -- Enable All  the constraints back

-- You may ignore the errors that shows the table without Auto increment field.

Answer 6

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.