How to reseed/replace identity columns on existing data

Question

Hey all I want to reseed my IDENTITY COLUMN values starting from 1 I know how to do this with DBCC CHECKIDENT, however, I would like to replace the value in all existing rows.. This table has a little over 2 million rows.

What is the best approach for this task?

score 6 · Accepted Answer · edited May 23 '17 at 12:33

6

You can simply add a new identity column, a la How to add a new identity column to a table in SQL Server?. Just delete the old column and re-add it. This will break any foreign keys, of course, but I assume since you are re-numbering everything I am guessing that's ok.

edited May 23 '17 at 12:33

Community

1
1

answered Jan 08 '12 at 06:45

Chris Shain

50,833
6
93
125

2

If you have forign key relationships, I'd suggest preserving the old column until you can update all the child tables with the new id value. They you can drop it. – HLGEM Jan 09 '12 at 20:22
The problem with that is that SQL Server cannot have multiple identity columns in a table: http://stackoverflow.com/questions/349092/can-a-sql-server-table-have-two-identity-columns. You'd need to remove the identity attribute from the old one first. – Chris Shain Jan 09 '12 at 20:23
1

YEs, that is true you need to remove the identity property. – HLGEM Jan 09 '12 at 20:24

Oleg Dok · Answer 2 · 2012-05-13T07:39:53.027

See this example how to replace values, but RESEED is something else:

CREATE TABLE t (id INT IDENTITY)
GO
INSERT t DEFAULT VALUES
GO 25

SET IDENTITY_INSERT t ON

delete t 

OUTPUT DELETED.Id+100 INTO T(Id)

SET IDENTITY_INSERT t Off

SELECT * FROM t

DROP TABLE t

An example of reseed:

DBCC CHECKIDENT('YourTableName', 150, reseed)

AND

If you have to replace the value - with 2M rows it definitely have to take time

How to reseed/replace identity columns on existing data

2 Answers2