Get current value from a SQL Server SEQUENCE

Question

I want to get the current value from my sequence - the same value that is shown in the sequence properties window SQL Server Management Studio

My sequence is created with this statement:

CREATE SEQUENCE [OrderNumberSequence]
    as int
    START WITH 4000
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
    NO CACHE;
GO

I have tried this SQL from MSDN – but the result is that my number is increasing with 5 for every time I run the query

DECLARE  
  @FirstSeqNum sql_variant
, @LastSeqNum sql_variant
, @CycleCount int
, @SeqIncr sql_variant
, @SeqMinVal sql_variant
, @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'[OrderNumberSequence]'
, @range_size = 5
, @range_first_value = @FirstSeqNum OUTPUT 
, @range_last_value = @LastSeqNum OUTPUT 
, @range_cycle_count = @CycleCount OUTPUT
, @sequence_increment = @SeqIncr OUTPUT
, @sequence_min_value = @SeqMinVal OUTPUT
, @sequence_max_value = @SeqMaxVal OUTPUT ;

-- The following statement returns the output values
SELECT
  @FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal
, @CycleCount AS CycleCount
, @SeqIncr AS SeqIncrement
, @SeqMinVal AS MinSeq
, @SeqMaxVal AS MaxSeq ;

Is there a way that I can get the value without changing the number?

Euonymus

Answer 1

You can select the current_value from sys.sequences:

SELECT current_value FROM sys.sequences WHERE name = 'OrderNumberSequence' ;

DEMO

Wrench

I tried to store the value of current_value in a variable I created with DECLARE only to find out current_value is of type sql_variant. That came as a surprise. I expected/needed an int or bigint and had to use CONVERT. – Shill

For those who were wondering what @BernhardDöbler was saying, the syntax is SELECT CAST(current_value AS int) FROM sys.sequences .... – Polley

Answer 2

Pretty old question, but if you are on SQL2017+ and wondering how to get the correct value for a sequence, even when no value at all has been taken from it, you should take into account column last_value_used too.
When the sequence has been created for the first time, the simple statement SELECT current_value FROM sys.sequences WHERE name = 'OrderNumberSequence' ; will return the first value that the sequence will be returning after the first next value from ... Then, after the first next value from, that statement will return the same result!

Instead, it would be better to have NULL when no value has ever been taken from the sequence, e.g. when the sequence has just been created or restarted.

Table sys.sequences has a last_value_used column that is NULL when no value has been taken from the sequence or the sequence has just been restarted.
In other words, while current_value column reports the first value that will be using the sequence on first "next value from" and from that point on will be reporting the "current actual value", column last_value_used will be NULL until you ask for the first value.
From that point on, last_value_used will be always equal to current_value.

You can use sample snippet down here to test it by yourself.

drop sequence if exists dbo.MySequence
go

-- 1. create sequence without touching it
CREATE SEQUENCE [dbo].[MySequence] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 2147483647
 CYCLE 
 CACHE 
GO

-- 2. observe current_value and last_used_value columns (should be current_value = 1, last_used_value = null)
SELECT 'Observe initial state of sequence after creation' msg, current_value, last_used_value FROM sys.sequences WHERE name = 'MySequence'

-- 3. get current value of sequence, based on last_used_value nullable state
declare @CurrentValue sql_variant
SELECT @CurrentValue = CASE WHEN last_used_value IS NULL THEN NULL ELSE current_value END
FROM sys.sequences WHERE name = 'MySequence'

select 'Current actual value' msg, @CurrentValue current_value -- should be null

-- 4. get next value from sequence
select 'Get first value from sequence' msg, next value for dbo.MySequence value -- should be 1

-- 5. observe current_value and last_used_value columns (should be current_value = 1, last_used_value = 1)
SELECT 'Observe state of sequence after getting first value' msg, current_value, last_used_value FROM sys.sequences WHERE name = 'MySequence'

-- 6. get current value of sequence, based on last_used_value nullable state
SELECT @CurrentValue = CASE WHEN last_used_value IS NULL THEN NULL ELSE current_value END
FROM sys.sequences WHERE name = 'MySequence'

select 'Current actual value' msg, @CurrentValue current_value -- should be 1

-- 7. get another value from seqeunce
select 'Get second value from sequence' msg, next value for dbo.MySequence value -- should be 2

-- 8. observe current_value and last_used_value columns (should be current_value = 2, last_used_value = 2)
SELECT 'Observe state of sequence after getting second value' msg, current_value, last_used_value FROM sys.sequences WHERE name = 'MySequence'

-- 9. get current value of sequence, based on last_used_value nullable state
SELECT @CurrentValue = CASE WHEN last_used_value IS NULL THEN NULL ELSE current_value END
FROM sys.sequences WHERE name = 'MySequence'

select 'Current actual value' msg, @CurrentValue current_value -- should be 2

Zeidman

Answer 3

If you are using PostgreSQL, you can use this:

SELECT last_value FROM <sequence_name>;

Kimberelykimberlee

Answer 4

For T-Sql, you can create a function:

CREATE FUNCTION [dbo].[fnCurrentSequenceValue](@seqName as varchar(50)) RETURNS varchar(max)
AS
BEGIN
-- potentially you can remove unwanted dbo or [] characters or modify sequence name here
    SET @seqName = Replace(Replace(Replace(@seqName,'[',''), ']',''),'dbo.','');
    RETURN convert(varchar(max),(select current_value FROM sys.sequences WHERE name=@seqName));
END

Courtyard

Okay, but outside of wrapping this in a function, how does this compare to the well-established answers previously posted? Are you doing anything different, or just wrapping this in a function. (Which, I should note, wasn’t what the question is about.) – Hedveh