Set a new, specific value for SQL sequence

Question

I have created a sequence to my database as follows:

DECLARE @max int;
SELECT @max = MAX(customernumber)+1
FROM organisation

exec('CREATE SEQUENCE organisation_customernumber_sequence
    START WITH ' + @max +
'   INCREMENT BY 1;')

It works and can be used in the application as intended. However, now there's a requirement to have it start from the number 40000.

Somehow I've managed to not find a way to do this. What is the simplest way to set my organisation_customernumber_sequence to 40000 with a single query?

I haven't used sequences much, but isn't it just a case of using `STARTS WITH 40000`? — Nick.Mc, Jun 20 '17 at 05:36
Well, I actually found it. It would be ALTER SEQUENCE organisation_customernumber_sequence RESTART WITH 40000; You can answer that and I'll check it as correct answer. — Steve Waters, Jun 20 '17 at 05:43
I hate to take credit where credit is not due - why don't you answer it instead :) — Nick.Mc, Jun 20 '17 at 06:49

score 0 · Answer 1 · answered Jun 20 '17 at 10:15

0

ALTER SEQUENCE organisation_customernumber_sequence RESTART WITH 40000;

GO

answered Jun 20 '17 at 10:15

Steve Waters

3,348
9
54
94

Set a new, specific value for SQL sequence

1 Answers1