In SQL Server, we can use sequences to generate sequence numbers that increment by a specified amount. This means that any new number generated by the sequence will be the next sequential increment as specified in the sequence’s definition.

Normally, this is exactly what we want. We want each number to adhere to the increment that we specified when defining the sequence.

But what if we want to reset the sequence, so that the numbering starts all over again? In other words, we want to restart the sequence from the beginning. Or what if we want to reset the sequence so that it increments in a different range?

Fortunately, we can reset a sequence with the ALTER SEQUENCE statement.

Example

Suppose we create a sequence like this:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1;

And start using it:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerNumber,
    CustomerName
FROM Customers;

Result:

CustomerNumber  CustomerName      
--------------  ------------------
1               Palm Pantry       
2               Tall Poppy        
3               Crazy Critters    
4               Oops Media        
5               Strange Names Inc.

Here, I used the sequence to number the rows returned in a result set of a query. In this case there were five rows, and so the sequence incremented from 1 up to 5 (because when I created the sequence, I specified START WITH 1 and INCREMENT BY 1).

Now, if I run the same query again, the sequence will pick up from where it left off:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerNumber,
    CustomerName
FROM Customers;

Result:

CustomerNumber  CustomerName      
--------------  ------------------
6               Palm Pantry       
7               Tall Poppy        
8               Crazy Critters    
9               Oops Media        
10              Strange Names Inc.

This may or may not be the desired result. However, if we wanted the numbering to restart from 1 again, we can use the ALTER SEQUENCE statement to reset the sequence.

Here’s how we can reset the sequence:

ALTER SEQUENCE Sequence1 RESTART WITH 1;

We use the RESTART WITH argument to reset – or restart – the sequence. In this case I specified that it should restart with 1. We can alternatively just use RESTART without specifying the WITH part. When we do this, it will restart based on the original CREATE SEQUENCE options.

Now let’s run the SELECT query again:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerNumber,
    CustomerName
FROM Customers;

Result:

CustomerNumber  CustomerName      
--------------  ------------------
1               Palm Pantry       
2               Tall Poppy        
3               Crazy Critters    
4               Oops Media        
5               Strange Names Inc.

As expected, the sequence has been reset as specified.