SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.

But what if we want to change the sequence to use a different range?

For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.

Easy. We can use the ALTER SEQUENCE statement to do just that.

Example

Suppose we create a sequence like this:

CREATE SEQUENCE Sequence1
    START WITH 0
    INCREMENT BY 25
    MINVALUE 0
    MAXVALUE 100
    CYCLE;

This sequence cycles through a range between 0 and 100 in increments of 25. Once it reaches 100, it starts back at 0 and continues to cycle through indefinitely.

Here’s an example of using it in a query:

SELECT 
    NEXT VALUE FOR Sequence1 AS ArtistGroup,
    ArtistName
FROM Artists;

Result:

ArtistGroup  ArtistName            
-----------  ----------------------
0            Iron Maiden           
25           AC/DC                 
50           Allan Holdsworth      
75           Buddy Rich            
100          Devin Townsend        
0            Jim Reeves            
25           Tom Jones             
50           Maroon 5              
75           The Script            
100          Lit                   
0            Black Sabbath         
25           Michael Learns to Rock
50           Carabao               
75           Karnivool             
100          Birds of Tokyo        
0            Bodyjar               

16 row(s) returned

We used the sequence to generate the numbers for the ArtistGroup row. We can see that the numbers increment by 25 as specified, then start again at 0 once they reach the end of the sequence.

Now let’s change the sequence to use a different range:

ALTER SEQUENCE Sequence1
    RESTART WITH 200
    INCREMENT BY 25
    MINVALUE 200
    MAXVALUE 300
    CYCLE;

Now let’s run the query again:

SELECT 
    NEXT VALUE FOR Sequence1 AS ArtistGroup,
    ArtistName
FROM Artists;

Result:

ArtistGroup  ArtistName            
-----------  ----------------------
200          Iron Maiden           
225          AC/DC                 
250          Allan Holdsworth      
275          Buddy Rich            
300          Devin Townsend        
200          Jim Reeves            
225          Tom Jones             
250          Maroon 5              
275          The Script            
300          Lit                   
200          Black Sabbath         
225          Michael Learns to Rock
250          Carabao               
275          Karnivool             
300          Birds of Tokyo        
200          Bodyjar               

16 row(s) returned

So it’s the same query with the same results, except that the values in the ArtistGroup field are now in a different range.