Reset AutoIncrement in SQL Server after Delete

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

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.

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