SEQUENCE gets reset on Publish from SSDT DB Project
When you add a sequence to a database project and have it start at 1 and then deploy the project to a database, the first time it gets created correctly.
When you get more values and the sequence next value is 100 and then publish the project again, the publish generates an ALTER SEQUENCE dbo.SequencObject RESTART WITH 1; statement and that should not happen when the sequence object already exists.
Just re-read the original scenario described above and got a feeling that the start_value must've been tempered with. I haven't encountered a restart generated by SSDT.
In my case, I did a restart manually to align the sequence number with the max id of a table. This is when SSDT would generate a restart as expected due the different start_value setting between the model and the db.
Replying to the comment below, changing the START WITH in SSDT is a workaround but it does not address the issue that start_value and current_value can and sometimes need to be different.
we found if you change the START WITH value in SSDT, and do a publish, it alters the next sequence value, so it requires a deployment, but it works.
Just experienced this issue in our prod environment. What a nasty surprise! What we need is a way to set the current_value instead of a hard restart.
Maybe consider the addition of another option to either do a restart or set new current_value:
alter sequence dbo.seq restart with 1 -- For a hard reset
alter sequence dbo.seq set current with 1314 -- For a new current_value
Ben Miller commented
This is making things really difficult in the DevOps place. With this resetting things right now, we have to exclude sequences and then put code in the Pre-Deployment to detect whether or not the SEQUENCE exists and if not then create it. If you don't exclude sequences then you have to put code in the post deployment script to update the restart with for the max value of the table.