sql - How to create an auto-incrementing column that doesn't skip numbers after deleting a row? -

sql - How to create an auto-incrementing column that doesn't skip numbers after deleting a row? -

i'd have column that's (automatically) enumerating sequence of rows. have table:

nr name 1 test 2 test2 3 abc 4 def

in first delete lastly row followed adding new one. problem table looks this:

nr name 1 test 2 test2 3 abc 5 ghi

how can create resume 4 ?

the formal reply reseed value. here 1 description of do.

the real answer, though, not worry it. identity values not guaranteed sequential , there variety of reasons why gaps appear. without deletes. should used gaps in auto-incremented identity.

if need values sequential no gaps, there solutions. 1 calculate value on output:

row_number() on (partition id)

works quite well.

the other write trigger enforce rules want. know, though, folks write database engines know want identity gap-less. however, don't want take performance nail guarantee this, have reasonable compromise in terms of functionality , performance. if seek enforce sequential-ness without gaps, impact performance, particularly high-volume inserts , deletes.

sql sql-server ssms