Menu
I created a database in SQL Server with a couple of tables. I ran some tests and now am ready to deploy my solution, problem is, there is all sorts of data in the tables. I want to delete every row of all the tables created with my tests and put back the primary keys to zero. I tried delete which doesn't reset the primary keys index, and drop simply destroyed the table.
418
Truncate command reset the identity to its seed value. It requires more transaction log space than the truncate command. It requires less transaction log space than the truncate command. You require Alter table permissions to truncate a table.
IDENTITY Property during TRUNCATE TABLE. IDENTITY property is used in a table when you need to auto increase a number for a column. This means that when the first record is inserted IDENTITY column will become 1 and the next record will be 2 and so on.
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 .
You can try TRUNCATE TABLE which deletes all rows and resets identity seeds. However you will have to execute in a certain order if you have foreign keys. In which case you will need to delete from the child tables first, or drop the constraints and re-add them.
Also note that if you are using IDENTITY you really shouldn't expect the numbers to mean anything, or to be generated forever without gaps. You shouldn't care whether it starts at 1 vs. 22 or 65 - can you explain why the identity values need to be reset?
127
From: http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/
To set the value of the next ID to be 1, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 0)
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
