Menu
I seem to remember in MySQL when truncating a table the auto incremented index field would continue where it left off. So if said table was truncated with the highest id was 100, the next id after truncation would be 101.
Is there a way to do this in SQL Server? I truncated my table with over 1000 rows, but after truncating the next new id went back to 1 in my identity column. I would like for it to continue.
429
Identity columns It's used to create key values in tables. DELETE statements don't reset identity columns. It means new rows will have the next value in the identity columns. But, TRUNCATE does reset identity columns.
0.2), a TRUNCATE statement can also specify the DROP ALL STORAGE clause to release the space currently allocated for a table to the containing tablespace.
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.
In SQL Server 2012 - MS had introduced 'Identity Cache'. This feature had a bug of auto-incrementing ID column by '1000'. For example, if ID columns are 1, 2 and when an ID Jump happens the next ID column is 1003, instead of '3'. There are workarounds available to fix this issue.
DBCC CHECKIDENT (<table name>, reseed, 1000) should do the trick.
Note that the the reseed shown above will mean that the next number will be 1001, so set to 999 if you want the next ID to be 1000.
This article explains a bit more.
186
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
