SQL Server: How to reset an IDENTITY column
I recently filled up a big table that had an IDENTITY column. IDENTITY columns are "auto-numbering" columns that can be set up to increment every time you insert a new row in a SQL Server table. This can be used to make a convenient ID column.
But if you delete records from a table, or even TRUNCATE the table, the IDENTITY column will not go back to zero, or whatever initial value you set up. To do this, you use a DBCC CHECKIDENT command:
DBCC CHECKIDENT('MyTable')
This example sets the IDENTITY column of MyTable back to its original seed value, if that is possible.
How would you be able to reset the column instead of the table? Like how to reset one without loosing data.
ReplyDeleteIf I understand you correctly, the same command will work. So, for example, you have an identity column. You put in 1,000 records, then delete the last 500. Your identity column will keep counting fromm 1,001, but you want it to start counting from 501.
ReplyDeleteJust use DBCC CHECKIDENT('MyTable'). That will restart the identity column from its current maximum value, so the next value would be 501. Hope that helps.