restructure identity field in sql server

I have a table with identity field of int, i have the following data

ID      Name
-------------
1       Item1
4      开发者_Python百科 Item2
9       Item3
....

I want to reorder my identity filed like

1 Item1
2 Item2
3 Item3
...

You cannot update an existing identity value. Identity_Insert is only for allowing you to specify the identity column with new inserts. Your best bet is probably to create a new table with the same definition (including the identity column) and insert all records minus the identity column into the new table. Then drop the old table, and rename the new table with sp_rename. Note that you'll have to drop any foreign keys with this approach.

One option is to add a new integer field to hold the sequential values, then turn Identity Insert ON for your original column, then migrate the new values over.

One of the limitations of an IDENTITY column in SQL Server is that it cannot be updated directly. The best option is probably to create a new table.

In general, if you care about the ordering of values then don't use IDENTITY because it's too difficult to control the ordering. Use a regular column instead. SQL Server 2011 onwards supports sequences which give you much more control and don't require the target column to be read-only.