Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i have some big tables with several million records and want to change the identity(1,1) to identity(1,5). what would be the best way to do that?

share|improve this question

migrated from Apr 25 '10 at 15:48

This question came from our site for computer enthusiasts and power users.

up vote 0 down vote accepted

No answer yet?

i see a way like this on a in place migration:

INTO Backup_Table
DROP Source_Table
CREATE Source_Table (SerialNo IDENTITY(1,5) NOT NULL, ...)
INSERT Source_Table(*)
FROM Backup_Table

This has to be done for all the tables in a database.

What do you think?

share|improve this answer
On this approach one have to recreate all the constraints and indexes for new... The better way is to get a full-SQL-script from the table and change the Value of identity-step from 1 to 5 with an editor (search & replace). But how to restore the data? – Ice Jul 25 '10 at 10:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.