How to create Auto Increment in Sql Server

originally posted here: https://koalatea.io/sql-server-auto-increment/

Intro

Recently I was migrating a postgresql database to sql server. One issue I ran into was migrating the Id columns and keeping the same values as before. SQL server does not let you change a column to identity and does not allow you to edit the identity column. Online resources suggested creating a new column or reinserting each row, however, these were not great options as I was using Azure DB migration tool for a large db and was not guaranteed to have sequential ids since the old db had deletes. My solution was to implement my own auto increment for the Id column.

Setup

The example is somewhat complicated, but let me set up a small example.

We start with an employee table.

CREATE TABLE employees (
    id int not null,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
);

Notice that we use int for id as we can not map the old data to identity when using Azure migration tool.

Now, we can insert some existing data from the old database.

insert into employees (id, first_name, last_name) 
    values 
    (1, 'keith', 'holliday'),
    (2, 'jon', 'doe'),
    (4, 'jon', 'dane');

Notice that we do not have row 3. That is because in the old database, the row was deleted. I can not reset the Ids, because the old database has many relationships that depend on those ids.

Right now, if I insert, the id column will not auto increment. To resolve this, let's create a sequence for the table and set the next value as default for the id column.

CREATE SEQUENCE employees_ids 
    START WITH 5
    INCREMENT BY 1;  

We created a sequence that will increment by 1 and starts at 5, which is one more than the current max id in the table. We now need to set the default value for the id column.

alter table employees 
add constraint employees_id
default (next value for employees_ids) for id;

The above creates a constraint that will crab the next value in our sequence on insert. We can test this out by adding a new employee.

insert into employees (first_name, last_name) 
    values 
    ('ash', 'ketchum');

id	first_name	last_name
1	keith	holliday
2	jon	doe
4	jon	dane
5	ash	ketchum

Finally, we can now make this our primary key.

ALTER TABLE employees 
   ADD CONSTRAINT PK_employees_id PRIMARY KEY (id);