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

Geometrize

It sounds like you're trying to achieve the so-called "gapless" identifier column. If so, I'd advise against it; it causes more headaches than whatever gains you'll get. – Welltimed

My advice: Do not do this! If these numbers are keys then reusing them is a bad idea. For example, using your code, Bob has a key of 3 but after you delete Bob's row and renumber the table, key 3 is assigned to Jessica. What happens if Bob now turns up with an employment contract with "Employee ID 3" on it? What if it is an ordering system and Bob turns up with an invoice saying "Order number 3"? This will have potentially devastating legal and compliance implications, let alone the data integrity issues. – Registry

If the numbers are not keys and you only need them at the reporting stage then only add them in at the reporting stage, using functions like RANK(), DENSE_RANK() and ROW_NUMBER(). – Registry

@GreenstoneWalker Thanks; they're not keys. We use them for numerous algorithms. And yes, I would never re-order something if it's a key. – Geometrize

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;

Emulation