-1

I've got a table with two columns, body and id. The body is a string field and id an auto incrementing integer.

For example:

body | id
test   1
test2  2
hello  3
hi     4

What I'd like to do is understand how to fix any gaps within the auto-increment if a record is deleted. For example if I deleted id 2, I'd like the other id fields to shift. So id 3 becomes id 2, 4 becomes 3, etc. That way my id field is still sequential with no missing numbers.

Does sql offer a method to fixing this problem? I've found conflicting information on this potentially being a bad idea, but having no gaps would make what I'm building much easier to navigate.

Thank you!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Melvin Rowls
  • 19
  • 6
  • 4
    Embrace the GAP. https://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values – John Cappelletti Oct 07 '22 at 19:34
  • 2
    If you care about gaps, you should not be using identity. The generated values are intended to be used as incremental but meaningless surrogate keys. Don't try to "fix" them. You can use `ROW_NUMBER` in your query to generate gapless values. – Dan Guzman Oct 07 '22 at 19:34
  • *Does sql offer a method to fixing this problem* Can you elaborate on exactly why this is a problem and what needs fixing? – Stu Oct 07 '22 at 20:09

1 Answers1

0

If id field is also your primary key or your have lots of rows in your table then it is not a good idea to update it.

I suppose what your are trying to achieve is to add a row number, see SQL Server ROW_NUMBER Function

SELECT ROW_NUMBER() OVER( ORDER BY field1, field2) row_num, other_fields
FROM table

It will create sequential numbers according to the order.

You can combine this with a CTE (Common Table Expression) so you can use it to filter your rows as well.

You can also use this CTE to update your id column as well.

endo64
  • 2,269
  • 2
  • 27
  • 34