Replace primary key Identity with sequence starting with next AUTO_INCREMENT value

Question

I currently have a table, Messages. The ID column is the primary key and is populated using an Identity.

x-----------------------------x
|    ID       |  Contents     | 
x-------------|---------------x
|    1        |  01234        |
|    2        |  56789        |
x-----------------------------x

I want to remove the identity on the ID column and replace it by populating the default value of ID from a sequence, Messages_ID_Sequence.

This is easy enough to accomplish in SSDT. However, when I deploy the database project, the first value in Messages_ID_Sequence is 1. It's obvious why when I look at the SQL generated for Messages_ID_Sequence:

CREATE SEQUENCE [dbo].[CommandMsgs_MSGID_Sequence]
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO CYCLE
    CACHE 10

The problem happens when I try to insert a new Message record. The first value from the sequence, 1, populates the ID column of the new record and causes a violation of the primary key constraint.

So how can I ensure that when Messages_ID_Sequence is created, it starts at the next ID that the identity would have provided? The Build Action on Messages_ID_Sequence's creation script must be set to Build so the solution can't involve anything that SSDT doesn't understand.

Answer 1

You can construct dinamically the SQL sentence to create your sequence, so it starts at your last identity.

/* Read the last identity value */
DECLARE @value bigint;
select @value = max(id_field) from my_table;

/* Create the sequence starting at that value */
DECLARE @sql nvarchar(max);
SET @sql = N'CREATE SEQUENCE [dbo].[CommandMsgs_MSGID_Sequence] AS BIGINT START WITH ' + cast(@value as nvarchar(20)) + ' INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 10;';
EXEC SP_EXECUTESQL @sql;

Or update at any moment your sequence so it starts at the desired value.

/* Read the last identity value */
DECLARE @value bigint;
select @value = max(id_field) from my_table;

/* Create the sequence starting at that value */
DECLARE @sql nvarchar(max);
SET @sql = N'ALTER SEQUENCE StreamEntrySequence RESTART WITH ' + cast(@value as nvarchar(20)) + ';';
EXEC SP_EXECUTESQL @sql;