sql - Filling the gaps in values of IDENTITY column

Question

I have a table with an IDENTITY column

[Id] int IDENTITY(1, 1) NOT NULL

After some rows beeing added/removed I end with gaps in Id values:

Id   Name
---------
1    Tom
2    Bill
4    Kate

Is there an easy way to compress the values to

Id   Name
---------
1    Tom
2    Bill
3    Kate

?

Answer 1

I would strongly recommend that you leave the identity values as they are.

if this ID column is used as a foreign key on another table, changing them will get complicated very quickly.

if there is some business logic where they must be sequential then add a new column ID_Display where you can update them using ROW_NUMBER() and keep them pretty for the end user. I never let end users see and/or dictate how I create/populate/store the data, and if they are bothering you about the IDs then show them some other data that looks like an ID but is not a FK or PK.