SQL Server 2008, add 'x' to auto increment value

Question

I am using SQL Server 2008 and what I am trying to do is:

update Daily set ReferenceNo = ReferenceNo + 100000

I can't do this as this is field is as follows: [ReferenceNo] [bigint] IDENTITY(1,1) NOT NULL,

If I try to change remove the auto increment and save the table to do what I need to do I get the following error:

enter image description here

Is there a way I can do this without dropping the table?

score 5 · Accepted Answer · answered Oct 19 '12 at 12:06

5

It's safety feature of SQL Server Management Studio - but you can turn it off :-)

Go to Tools > Options and then:

enter image description here

After you've disabled that option, you should be able to do what you want to do in the visual designer.

answered Oct 19 '12 at 12:06

marc_s

732,580
175
1,330
1,459

score 2 · Answer 2 · answered Oct 19 '12 at 12:07

I've figured it out, for anyone else who has this problem, do the following:

In SQL Server 2008, go to Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation. Turn this option off and you will be able to save the tables again.

score 0 · Answer 3 · edited May 23 '17 at 11:56

The error reveals that the IDENTITY column is ALSO your clustering key.

Check out this answer to a previous question.

It gets complicated due to it being an IDENTITY column, which by design cannot be UPDATEd.
Because you're updating a clustering key, the data is in fact deleted and re-inserted. Given that it has to rewrite all the data, it's no more trouble to just recreate the table - triggers, synonyms, functions, schema-bound views, and foreign keys aside - that is.

SQL Server 2008, add 'x' to auto increment value

3 Answers3