0

In SQL Server 2012, is there a way to know if a Sequence has recycled?

Or some sort of trigger to know if a Sequence is nearing its cycling phase.

I need to update a table whenever a Sequencerecycles.

1 Answer 1

0

There is no metadata view that is going to clearly give you this information. Your best bet would be to have a scheduled process that runs every few minutes and looks at sys.sequences and evaluates if the current_value is less than the prior current_value, in which case you would know that it had cycled.

You can also compare the current_value to the maximum_value to see how close you are to hitting the cycle point.

A very basic view of this would be the following with a small sequence, you can see the initial values remaining, what it looks like after using a few sequences, and then how it gets reset as the sequence cycles.

CREATE SEQUENCE dbo.TestSeq 
AS SMALLINT
MINVALUE 1
MAXVALUE 10
START WITH 1
INCREMENT BY 1
CYCLE;

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 5

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 6

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.