0

QUESTION: Is it possible to roll back the maximum value of a column(using IDENTITY) after deleting some rows?

PROBLEM: The value of column(id) doesn't reseed/roll back to the current maximum value of the column, it keeps incrementing with the last known max.

johnprima
  • 25
  • 1
  • 10
  • 1
    answer: no, you don't want to reset this value – Iłya Bursov Sep 18 '14 at 01:17
  • what RDBS are you using? – ZeRaTuL_jF Sep 18 '14 at 01:17
  • 1
    possible duplicate of [Reset Identity Seed after deleting records in SQL Server](http://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server) – shf301 Sep 18 '14 at 01:20
  • 2
    Why does it matter if it resets? Let the database handle the identity for you. If you need to change the result, then do that in your view or presentation logic with a `row_number`. No need to reseed identity fields. – sgeddes Sep 18 '14 at 01:21
  • @sgeddes I see. So I should only need to use identity column for unique values. – johnprima Sep 18 '14 at 01:33

1 Answers1

2

I'm assuming you're using SQL Server.

The behaviour you described is the way IDENTITY columns work by design. The value for new rows is only ever incremented. This is normally used to ensure uniqueness of generated values. If you delete records, it leaves a gap in numbers. There's even a remark on TechNet:

If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property.

You can reseed identity if needed or you can enter any value explicitely into an idenity column when using SET IDENTITY_INSERT ON but that's not a standard usage of those columns.

Szymon
  • 42,577
  • 16
  • 96
  • 114