IDENTITY column values are immutable.
However it is possible to switch the table metadata to remove the IDENTITY property, do the update, then switch back.
Assuming the following structure
CREATE TABLE Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
X VARCHAR(10)
)
INSERT INTO Test
OUTPUT INSERTED.*
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'
Then you can do
/*Define table with same structure but no IDENTITY*/
CREATE TABLE Temp
(
ID INT PRIMARY KEY,
X VARCHAR(10)
)
/*Switch table metadata to new structure*/
ALTER TABLE Test SWITCH TO Temp;
/*Do the update*/
UPDATE Temp SET ID = ID + 1;
/*Switch table metadata back*/
ALTER TABLE Temp SWITCH TO Test;
/*ID values have been updated*/
SELECT *
FROM Test
/*Safety check in case error in preceding step*/
IF NOT EXISTS(SELECT * FROM Temp)
DROP TABLE Temp /*Drop obsolete table*/
In SQL Server 2012 it is possible to have an auto incrementing column that can also be updated more straightforwardly with SEQUENCES
CREATE SEQUENCE Seq
AS INT
START WITH 1
INCREMENT BY 1
CREATE TABLE Test2
(
ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY,
X VARCHAR(10)
)
INSERT INTO Test2(X)
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'
UPDATE Test2 SET ID+=1