Reset AutoIncrement in SQL Server after Delete

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.

I figured it out. It's:

 DBCC CHECKIDENT ('tablename', RESEED, newseed)

I want to add this answer because the DBCC CHECKIDENT-approach will product problems when you use schemas for tables. Use this to be sure:

DECLARE @Table AS NVARCHAR(500) = 'myschema.mytable';
DBCC CHECKIDENT (@Table, RESEED, 0);

If you want to check the success of the operation, use

SELECT IDENT_CURRENT(@Table);

which should output 0 in the example above.

You do not want to do this in general. Reseed can create data integrity problems. It is really only for use on development systems where you are wiping out all test data and starting over. It should not be used on a production system in case all related records have not been deleted (not every table that should be in a foreign key relationship is!). You can create a mess doing this and especially if you mean to do it on a regular basis after every delete. It is a bad idea to worry about gaps in you identity field values.

What about this?

ALTER TABLE `table_name`
  MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0;

This is a quick and simple way to change the auto increment to 0 or whatever number you want. I figured this out by exporting a database and reading the code myself.

You can also write it like this to make it a single-line solution:

ALTER TABLE `table_name` MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0;

To reset every key in the database to autoincrement from the max of the last highest key:

Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)'

Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED)'

If you just want to reset the primary key/sequence to start with a sequence you want in a SQL server, here's the solution -

IDs:

99 100 101 1200 1201...

1. Drop rows with IDs >= 1200. (Be careful if you have foreign key constraints tied to these which has to be dealed with or deleted too to be able to delete this.)
2. Now you want to make sure you know the MAX ID to be sure:

declare @max_id as int = (SELECT MAX(your_column_name) FROM your_table)+1;

(Note: +1 to start the ID sequence after max value)

3. Restart your sequence:

exec('alter sequence your_column_name restart with ' + @max_id);

(Note: Space after with is necessary) Now new records will start with 102 as the ID.

I know this is an old question. However, I was looking for a similar solution for MySQL and this question showed up.

for those who are looking for MySQL solution, you need to run this query:

// important!!! You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.
   
ALTER TABLE <your-table-name> AUTO_INCREMENT = 100

documentation