Re-structure sequence in existing SQL Server table

Question

I have a SQL Server 2016 table and it has than 10,000 rows. The Id column is a sequential id incremented by 1. But that Id is not in the correct order as shown in the screenshot below where 330 is missing. Is there any way to correct this sequence in an existing table?

I've tried like below but it's not working.

CREATE SEQUENCE contacts_seq
AS BIGINT
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 99999
   NO CYCLE
   CACHE 10;

you have to ask yourself why the value of a surrogate key would matter? Hint: it doesn't. — Mitch Wheat, May 22 '20 at 06:04
https://stackoverflow.com/questions/29819849/updating-identity-column-of-a-table-with-consecutive-numbers-through-sql-stored — Mitch Wheat, May 22 '20 at 06:06

score 1 · Answer 1 · answered May 22 '20 at 07:52

There is no point of fixing this - you are going to perform a operation which will block the access to your table and may affect your application performance.

And then, someone can delete the record X and you will be at the same position again.

Generally, the perpouse of id is to uniquely identify a row. If for some visualization reasons you do not want to have gaps you can use RANKING functions to fix this.

For example:

SELECT ROW_NUMBER () OVER (ORDER BY id) AS [ID]

Re-structure sequence in existing SQL Server table

1 Answers1