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?
This question is related to
sql-server
auto-increment
delete-row
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.
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.
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
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)
I figured it out. It's:
DBCC CHECKIDENT ('tablename', RESEED, newseed)
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.
If you're using MySQL, try this:
ALTER TABLE tablename AUTO_INCREMENT = 1
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
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)
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
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;
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.
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.
I figured it out. It's:
DBCC CHECKIDENT ('tablename', RESEED, newseed)
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.
I figured it out. It's:
DBCC CHECKIDENT ('tablename', RESEED, newseed)
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
If you're using MySQL, try this:
ALTER TABLE tablename AUTO_INCREMENT = 1
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.
I figured it out. It's:
DBCC CHECKIDENT ('tablename', RESEED, newseed)
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;
Source: Stackoverflow.com