Get current value from a SQL Server SEQUENCE

sequence, sql-server-2012, t-sql

Solution

You can select the `current_value` from `sys.sequences`:

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

DEMO

Problem

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?

Original source