MSSQL - Creating a table with an auto increment Primary key or adding it later

In MSSQL you can set auto increment for a column using the IDENTITY property. This will often be used on the primary key column, but it can be used on any column, there can just only be one identity column on a given table. We can create a table with an int as primary key starting at 1 and incremented by 1 using the following script:

CREATE TABLE SomeTable(
  Id INT IDENTITY(1,1) PRIMARY KEY (ID) NOT NULL,
  SomeText VARCHAR(MAX) NOT NULL,
  CreationTime DATETIME2 NOT NULL DEFAULT GETDATE(),
)

INSERT INTO [dbo].[SomeTable] ([SomeText]) VALUES ('Some text');

The first parameter of IDENTITY(1,1) is the starting point (seed) and the second is the increment. The above will start at 1 and increment by 1 for every insert.

Adding the column later

We can add a column with IDENTITY after table creation, but we cannot add IDENTITY incrementation to an already existing column. If we have the following table:

CREATE TABLE SomeTable(
  SomeText VARCHAR(MAX) NOT NULL,
  CreationTime DATETIME2 NOT NULL DEFAULT GETDATE(),
)

INSERT INTO [dbo].[SomeTable] ([SomeText]) VALUES ('Some text')

Then we can add an INT PRIMARY KEY column with IDENTITY using an ALTER TABLE statement:

ALTER TABLE [dbo].[SomeTable]
   ADD Id INT IDENTITY(1,1)
       CONSTRAINT PK_SomeTable PRIMARY KEY

INSERT INTO [dbo].[SomeTable] ([SomeText]) VALUES ('Some text 2');

Resource on adding IDENTITY later

The above adds a column like in the first example. The table will contain two rows with Id 1 and 2, so already existing rows will have an Id assigned to them after the operation.

That is all

I hope you found this helpful, please leave a comment down below with your thoughts!