Menu
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity defined.
But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.
How can I reset the identity column after I deleted the records so that the column has sequence in ascending numerical order?
The identity column is not used as a foreign key anywhere in database.
757
Delete does not reset identity values. Let's see how the truncate command behaves with the identity values. First, execute the command in example 5 to delete all rows of a table.
Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method. Syntax : DBCC CHECKIDENT ('table_name', RESEED, new_value); Note : If we reset the existing records in the table and insert new records, then it will show an error.
To retain the identity counter, use DELETE instead. If you are set upon truncating the table, you can manually look up the maximum ID before truncating, and then reseed the table using DBCC CHECKIDENT .
The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is:
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0); GO It was not supported in previous versions of the Azure SQL Database but is supported now.
Thanks to Solomon Rutzky the docs for the command are now fixed.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
