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?

Answer 1

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.