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

User · Answer

I figured it out  It s    DBCC CHECKIDENT   tablename   RESEED  newseed

User · Answer

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

User · Answer

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

User · Answer

I figured it out  It s    DBCC CHECKIDENT   tablename   RESEED  newseed

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Several answers recommend using a statement something like this   DBCC CHECKIDENT  mytable  RESEED  0    But the OP said  deleted some records   which may not be all of them  so a value of 0 is not always the right one  Another answer suggested automatically finding the maximum current value and reseeding to that one  but that runs into trouble if there are no records in the table  and thus max   will return NULL  A comment suggested using simply  DBCC CHECKIDENT  mytable    to reset the value  but another comment correctly stated that this only increases the value to the maximum already in the table  this will not reduce the value if it is already higher than the maximum in the table  which is what the OP wanted to do   A better solution combines these ideas  The first CHECKIDENT resets the value to 0  and the second resets it to the highest value currently in the table  in case there are records in the table   DBCC CHECKIDENT  mytable  RESEED  0  DBCC CHECKIDENT  mytable    As multiple comments have indicated  make sure there are no foreign keys in other tables pointing to the deleted records  Otherwise those foreign keys will point at records you create after reseeding the table  which is almost certainly not what you had in mind

User · Answer

If you re using MySQL  try this   ALTER TABLE tablename AUTO INCREMENT   1

User · Answer

Several answers recommend using a statement something like this   DBCC CHECKIDENT  mytable  RESEED  0    But the OP said  deleted some records   which may not be all of them  so a value of 0 is not always the right one  Another answer suggested automatically finding the maximum current value and reseeding to that one  but that runs into trouble if there are no records in the table  and thus max   will return NULL  A comment suggested using simply  DBCC CHECKIDENT  mytable    to reset the value  but another comment correctly stated that this only increases the value to the maximum already in the table  this will not reduce the value if it is already higher than the maximum in the table  which is what the OP wanted to do   A better solution combines these ideas  The first CHECKIDENT resets the value to 0  and the second resets it to the highest value currently in the table  in case there are records in the table   DBCC CHECKIDENT  mytable  RESEED  0  DBCC CHECKIDENT  mytable    As multiple comments have indicated  make sure there are no foreign keys in other tables pointing to the deleted records  Otherwise those foreign keys will point at records you create after reseeding the table  which is almost certainly not what you had in mind

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I figured it out  It s    DBCC CHECKIDENT   tablename   RESEED  newseed

User · Answer

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

User · Answer

If you re using MySQL  try this   ALTER TABLE tablename AUTO INCREMENT   1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I figured it out  It s    DBCC CHECKIDENT   tablename   RESEED  newseed

User · Answer

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

User · Answer

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

[sql-server] Reset AutoIncrement in SQL Server after Delete

Examples related to sql-server

Examples related to auto-increment

Examples related to delete-row