Reset A Sequence

Question

I've read several posts about the TSQL Identity Bug and have been playing around with using SEQUENCE. However, I'm curious about resetting the SEQUENCE on the ID value in a table. For an example:

CREATE SEQUENCE Inc
    AS INT
    START WITH 1
    INCREMENT BY 1
    CYCLE 
    CACHE 

-- Quick ability to redo everything if needed:
-- DROP SEQUENCE Inc

-- Our table grabs the next sequence for our ID field:
CREATE TABLE SequenceID(
    NewIDField INT DEFAULT NEXT VALUE FOR Inc,
    Name VARCHAR(100)
)

INSERT INTO SequenceID (Name)
VALUES ('John')
    , ('Tiffany')
    , ('Bob')
    , ('Jessica')

SELECT *
FROM SequenceID

-- We remove Bob:
DELETE FROM SequenceID
WHERE NewIDField = 3

-- ID value 3 is gone; it moves from 1 to 2 to 4
SELECT *
FROM SequenceID

INSERT INTO SequenceID (Name)
VALUES ('David')
    , ('Rosa')
    , ('Samuel')

-- ID 3 doesn't exist because the SEQUENCE grabs the next value from 4
SELECT *
FROM SequenceID

-- Let's just reset our ID
;WITH ResetIt AS(
    SELECT ROW_NUMBER() OVER (ORDER BY NewIDField) AS ID
        , NewIDField AS ExistingID
        , Name
    FROM SequenceID
)
UPDATE SequenceID
SET NewIDField = ResetIt.ID
FROM ResetIt
WHERE SequenceID.NewIDField = ResetIt.ExistingID

-- Yay!
SELECT *
FROM SequenceID

INSERT INTO SequenceID (Name)
VALUES ('Sarah')

-- Oh Sarah, tsk tsk.
SELECT *
FROM SequenceID

DROP TABLE SequenceID

Is there a way to automatically perform this with SEQUENCE where we can determine the last value and begin there (similar to a RESEED), as even with IDENTITY, if we remove a value, we still must RESEED, see:

CREATE TABLE IDID(
    ID INT IDENTITY(1,1),
    I INT
)

INSERT INTO IDID (I)
VALUES (1),(2),(3),(4)

SELECT *
FROM IDID

DELETE FROM IDID
WHERE ID = 3

INSERT INTO IDID (I)
VALUES (5),(6),(7)

SELECT *
FROM IDID

DROP TABLE IDID

Answer 1

After you perform your update, you'll have to run some dynamic SQL, as ALTER SEQUENCE only accepts a constant for the RESTART WITH clause:

DECLARE @resetSQL nvarchar(255) = 'ALTER SEQUENCE Inc RESTART WITH ' + (SELECT CAST(MAX(NewIDField)+1 as nvarchar(10)) FROM SequenceID);

exec sp_executesql @resetSQL;