Easy way to change Column ID (primary key) of a table to Auto increment

I started to use SQL Server recently and I still don't know the best way to do some things.

I created all the tables with a column ID as the primary key. Now when I try to insert values I get the following error:


"Cannot insert the value NULL into column 'id', table 'Project.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated."

What is a good, simple solution to this problem?

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(''); 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;

StackExchange Database Administrators Q#135420, answer score: 9