SQL Server sequence set current value

Question

I am using SQL Server 2012 (v11.0.2100) and I want to create a sequence that starts with a random (dynamic) number but I wasn't able to do this, also I put my effort to find a good solution for this but I haven't found something that will satisfy me.

The case that I tried and failed:

 DECLARE @sth bigint

 SET @sth = 1000

 ALTER SEQUENCE StreamEntrySequence
 RESTART WITH @sth;

Error :

Incorrect syntax near '@sth'

An ugly solution

 declare @sth bigint;
 declare @i bigint;

 SET @sth = 100000    ; 

 while @i<@sth;
 BEGIN
    SET @i= next value for StreamEntrySequence;
 END

Is there other way to set the current value or the start value to a random value? Maybe using server procedures?

What is the point of restarting a sequence with a random number? That seems rather strange. You would need to use dynamic sql for this. I would run away quickly from using a loop like that. — Sean Lange
– Sean Lange, Commented Nov 25, 2014 at 19:39
@SeanLange The point is to synchronize the sequence with the actual last value in the database. This can be done, for example, after the sequence is first added to an existing system, and used to synchronize the next sequence number to the last identity value. The number isn't random; it's a StreamEntryID — Ian Boyd
– Ian Boyd, Commented Mar 19, 2024 at 16:57

Tim Lehner · Accepted Answer · 2014-11-25 19:53:15Z

34

As has been mentioned, this would require dynamic SQL since alter sequence requires a constant for the restart argument.

You might do something like this, then:

DECLARE @sth bigint;
SET @sth = 1000;
DECLARE @sql nvarchar(max);
SET @sql = N'ALTER SEQUENCE StreamEntrySequence RESTART WITH ' + cast(@sth as nvarchar(20)) + ';';
EXEC SP_EXECUTESQL @sql;

answered Nov 25, 2014 at 19:53

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Valentin Over a year ago

I already have 2 tables with data that are FK in other tables. I want this 2 tables to use a shared sequence so both of them will have different and unique IDs. For one of them for the existing data I am able to reset the primary key to new values, but for the other one no.

Valentin Over a year ago

Thanks Tim Lehner, I totally forgot about execute sql. I am using too much EF :))

Code Novice Over a year ago

Seems like a better implementation would have been for Microsoft to simply allow the use of variables when creating or altering a sequence... this answer does get me what I need but it's unfortunate having to resort to dynamic sql only to create a sequence with a START value based on a variable. Again... this does work beautifully so thank you for that!

Khalid · Accepted Answer · 2018-04-30 07:18:46Z

20

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

http://msdn.microsoft.com/en-us/library/ff878572.aspx

answered Apr 30, 2018 at 7:18

Khalid

6559 silver badges23 bronze badges

Comments

Meysam Bagheri · Accepted Answer · 2024-10-06 07:29:10Z

0

According to MSDN SEQUENCES you can restart sequence using:

ALTER SEQUENCE [schema].[name]
RESTART WITH 3005000 -- any value

And test the sequence next value using this:

SELECT NEXT VALUE FOR [schema].[name] AS FirstUse;

answered Oct 6, 2024 at 7:29

Meysam Bagheri

885 bronze badges

Comments

kirodge · Accepted Answer · 2024-10-09 05:25:07Z

Here's an alternative solution (which preserves the original properties of the sequence and still does not require a loop to increment the sequence one at a time).

Basically, do it in three steps:

Change the INCREMENT value of the sequence, to the difference between the value you want it to have (i.e. the value required), and it's actual CURRENT_VALUE
Call Next Value on the sequence, to increment it by the value calculated in step 1
Restore the original INCREMENT value of the sequence

    DECLARE @SchemaName VARCHAR(255) = 'SCHEMA'
    DECLARE @SequenceName VARCHAR(255) = 'SEQUENCE'
    
    DECLARE @CurrentValueRequired INT
    SELECT @CurrentValueRequired = 1000000 --could be assigned from a SELECT query as well
    PRINT '@CurrentValueRequired=' + CAST(@CurrentValueRequired AS VARCHAR(20))
    
    DECLARE @CurrentValueActual BIGINT
    DECLARE @LastUsedValue BIGINT
    DECLARE @IncrementValue BIGINT
    DECLARE @CurrentValueDifference BIGINT
    SELECT @CurrentValueActual = CAST(CURRENT_VALUE AS BIGINT),
           @LastUsedValue = CAST(LAST_USED_VALUE AS BIGINT),
           @IncrementValue = CAST(INCREMENT AS BIGINT)
    FROM   SYS.SEQUENCES
    WHERE  NAME = @SequenceName
    SET @CurrentValueDifference = @CurrentValueRequired - @CurrentValueActual
    PRINT '@CurrentValueActual=' + CAST(@CurrentValueActual AS VARCHAR(30))
    PRINT '@LastUsedValue=' + CASE WHEN @LastUsedValue IS NULL THEN 'NULL' ELSE CAST(@LastUsedValue AS VARCHAR(30)) END
    PRINT '@IncrementValue=' + CAST(@IncrementValue AS VARCHAR(30))
    PRINT '@CurrentValueDifference=' + cast(@CurrentValueDifference AS VARCHAR(30))
    
    IF @CurrentValueDifference > 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX)
    
        -- If LAST_USED_VALUE is NULL, then the sequence has NEVER been accessed since it's creation. Call it once so that it's START_WITH value becomes the LAST_USED_VALUE.
        IF @LastUsedValue IS NULL
        BEGIN
            PRINT '@LastUsedValue IS NULL'
            SET @sql = N'DECLARE @NextValue BIGINT; SELECT @NextValue = NEXT VALUE FOR ' + @SchemaName + '.' + @SequenceName
            PRINT @sql
            EXEC SP_EXECUTESQL @sql;
            SELECT @CurrentValueActual = CAST(CURRENT_VALUE AS BIGINT), @LastUsedValue = CAST(LAST_USED_VALUE AS BIGINT) FROM SYS.SEQUENCES WHERE NAME = @SequenceName
            PRINT '@CurrentValueActual=' + CAST(@CurrentValueActual AS VARCHAR(30))
            PRINT '@LastUsedValue=' + CASE WHEN @LastUsedValue IS NULL THEN 'NULL' ELSE CAST(@LastUsedValue AS VARCHAR(30)) END
        END
    
        -- Set the INCREMENT value to the difference between the required CURRENT_VALUE and the actual CURRENT_VALUE
        SET @sql = N'ALTER SEQUENCE ' + @SchemaName + '.' + @SequenceName + ' INCREMENT BY ' + CAST(@CurrentValueDifference AS NVARCHAR(30)) + ';'
        PRINT @sql
        EXEC SP_EXECUTESQL @sql;
    
        -- Now call the sequence to increment it once by the difference value
        SET @sql = N'DECLARE @NextValue BIGINT; SELECT @NextValue = NEXT VALUE FOR ' + @SchemaName + '.' + @SequenceName
        PRINT @sql
        EXEC SP_EXECUTESQL @sql;
        SELECT @CurrentValueActual = CAST(CURRENT_VALUE AS BIGINT), @LastUsedValue = CAST(LAST_USED_VALUE AS BIGINT) FROM SYS.SEQUENCES WHERE NAME = @SequenceName
        PRINT '@CurrentValueActual=' + CAST(@CurrentValueActual AS VARCHAR(30))
        PRINT '@LastUsedValue=' + CASE WHEN @LastUsedValue IS NULL THEN 'NULL' ELSE CAST(@LastUsedValue AS VARCHAR(30)) END
    
        -- Set the INCREMENT back to the original value
        SET @sql = N'ALTER SEQUENCE ' + @SchemaName + '.' + @SequenceName + ' INCREMENT BY ' + CAST(@IncrementValue AS NVARCHAR(30)) + ';'
        PRINT @sql
        EXEC SP_EXECUTESQL @sql;
    END
    ELSE
    BEGIN
        PRINT 'NOTHING TO DO!'
    END

Collectives™ on Stack Overflow

SQL Server sequence set current value

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related