Fill in the blanks in the IDENTITY column values

Question

I have a table with an IDENTITY column

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

After some lines are added / removed, I end the spaces in the Id values:

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

Is there an easy way to compress values ​​to

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

?

Answer 1

I think it’s quite easy to create a second table with the same schema, import all the data (except for the identification column, of course, let the second table begin to renumber) from the first table, drop the first table and rename the Second name of the original.

There may be a problem if you have a ton of FK relationships to rebuild with other tables, etc.

Answer 2

I would strongly recommend leaving the values ​​of identity as they are.

ID , .

- -, , ID_Display, ROW_NUMBER() . / , // , , , , FK PK.

Answer 3

, , , , , ""... ​​ . , , .

Answer 4

, , .

, , - ?

, .

, . IDENTITY ( ), :

DELETE FROM db1.table
SELECT col1, col2, col3 /* leave out the IDENTITY column */ 
INTO db1.table FROM db2.table