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?

Answer 1

You can select the current_value from sys.sequences:

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

DEMO