Is it possible to reuse an identity field value after deleting rows in SQL Server 2005? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I will have these 5 Ids: 1, 2, 3, 4, 5. If I were to delete these rows, and then add five more, the new rows would have Ids: 6, 7, 8, 9, 10. Is it possible to let it start over at 1 again?
Jump to Post
Short of dropping/recreating the table, the only way to reset the identity values is to issue a TRUNCATE statement. Then the identity value for identity column for that table will reset to the seed value specified at create time.
Keep in mind that TRUNCATE gets rid of ALL the rows. …
All 3 Replies
We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.