My preferred method for a non-empty table is to use the ALTER TABLE ... SWITCH
method which is a meta-data-only operation.
This is a simple test-bed setup, consisting of the initial table, named dbo.SwitchTest1
, and the target table containing an IDENTITY
, named dbo.SwitchTest2
:
USE tempdb;
IF OBJECT_ID('dbo.SwitchTest1') IS NOT NULL
DROP TABLE dbo.SwitchTest1;
IF OBJECT_ID('dbo.SwitchTest2') IS NOT NULL
DROP TABLE dbo.SwitchTest2;
GO
CREATE TABLE dbo.SwitchTest1
(
ID INT NOT NULL
CONSTRAINT PK_SwitchTest1
PRIMARY KEY CLUSTERED
, SomeVal VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.SwitchTest2
(
ID INT NOT NULL
CONSTRAINT PK_SwitchTest2
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, SomeVal VARCHAR(50) NOT NULL
);
INSERT INTO dbo.SwitchTest1(ID, SomeVal)
VALUES (1, 'This is a test');
Here, we use the ALTER TABLE ... SWITCH
method to replace the data in dbo.SwitchTest2
table with data from dbo.SwitchTest1
, while maintaining the definition of dbo.SwitchTest2
including the IDENTITY
column. Although not strictly necessary, you probably want to rename any constraints included in the definition of dbo.SwitchTest2
after the fact to match what they used to be called in dbo.SwitchTest1
. I've done that below for the primary key.
BEGIN TRANSACTION
BEGIN TRY
ALTER TABLE dbo.SwitchTest1 SWITCH TO dbo.SwitchTest2;
DROP TABLE dbo.SwitchTest1;
EXEC sp_rename @objname = 'dbo.SwitchTest2'
,@newname = 'SwitchTest1', @objtype = 'object';
EXEC sp_rename @objname = 'PK_SwitchTest2'
, @newname = 'PK_SwitchTest1', @objtype = 'object';
EXEC sp_recompile @objname = 'dbo.SwitchTest1';
DBCC CHECKIDENT('dbo.SwitchTest1');
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
In the code above, I've also added a call to sp_recompile
- this causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. This is A Good Thing™ since the object_id for the table dbo.SwitchTest1
table has changed. Also, I'm running DBCC CHECKIDENT('<tablename>');
to cause the IDENTITY()
column to be updated with the next valid value to be used by an INSERT
operation.
Here, we're inserting a value into the SomeVal
column without specifying a value for the ID
column, which is now an IDENTITY
column:
INSERT INTO dbo.SwitchTest1 (SomeVal)
VALUES ('This is another test');
And the results:
SELECT *
FROM dbo.SwitchTest1;
