How do I add Auto Increment to Existing SQL ID Primary Key?

I have an empty table that I thought was auto increment until I tried adding my first record. That's when SQL told me this:

> Cannot insert the value NULL into column 'ID', table 'DB_9CF886_mcl959.dbo.Posts'; column does not allow nulls. INSERT fails.

So I tried to add the identity to it:

alter table dbo.Posts ADD ID INT IDENTITY(1,1) CONSTRAINT PK_Posts1 PRIMARY KEY CLUSTERED;

That gave me this error:

> Column names in each table must be unique. Column name 'ID' in table 'dbo.Posts' is specified more than once.

I could drop the table and recreate it, but I'd like to know how to FIX IT to add the identity to the ID column.

Screenshot of database: dbo.Posts

While it's true that you cannot add IDENTITY to an existing column, and that adding IDENTITY to an existing table means adding a new column - it's also true that you don't need to use IDENTITY - nor do you need to DROP anything either.

Instead, ALTER your existing ID column to use a SEQUENCE object for new values - which has (effectively) the same end-result as IDENTITY - but this way it will preserve your existing ID column values, preserve foreign-key references, and preserve your CLUSTERED index without needing a table rebuild.

Do this:

SET XACT_ABORT ON;

BEGIN TRANSACTION;

DECLARE @baseId int = ( SELECT ISNULL( MAX( ID ), 0 ) FROM dbo.Posts ) + 1;

-- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql
CREATE SEQUENCE dbo.PostsIdSequence START WITH @baseId INCREMENT BY 1;

ALTER TABLE dbo.Posts
    ADD CONSTRAINT DF_Posts_Sequence DEFAULT NEXT VALUE FOR dbo.PostsIdSequence FOR "ID";

COMMIT TRANSACTION;

Below is an example that uses SWITCH to move data into another table of identical schema that already has the IDENTITY.

--create sample table and data
CREATE TABLE dbo.AddIdentityExample(
	  IncrementalColumn int NOT NULL
		CONSTRAINT PK_AddIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_AddIdentityExample_OtherData
	ON dbo.AddIdentityExample(OtherData);
INSERT INTO dbo.AddIdentityExample VALUES
	  (1, 'Sample data one')
	, (2, 'Sample data two')
	, (3, 'Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.AddIdentityExampleStaging(
		  IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
			CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_AddIdentityExampleStaging_OtherData
		ON dbo.AddIdentityExampleStaging(OtherData);

	--move data from source table into new table
    ALTER TABLE dbo.AddIdentityExample
		SWITCH TO dbo.AddIdentityExampleStaging;
	DROP TABLE dbo.AddIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExampleStaging'
		, @newname = N'AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_AddIdentityExampleStaging'
		, @newname = N'PK_AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
		, @newname = N'idx_AddIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	--seed IDENTITY with current max column value
	DBCC CHECKIDENT(N'dbo.AddIdentityExample');
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO