When we create a sequence in SQL Server, we have the option of making it an incrementing sequence or decrementing.

By “decrementing”, I mean that the sequence decreases instead of increases. For example, if it starts at 100, the next value is 99, and then 98, and so on.

To create a sequence that decrements, all we do is provide a negative value for the INCREMENT BY argument.

Example

Here’s an example of creating a decrementing sequence:

CREATE SEQUENCE Sequence1
    START WITH 0
    INCREMENT BY -1;

In this case, we specified that the sequence starts at 0 and has an increment of -1 (negative one). This means that each number in the sequence will be one less than the previous number. Now when we use NEXT VALUE FOR to generate sequence numbers, they will decrement.

We can test this by running a SELECT query against a table:

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

Result:

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

As expected, the CustomerGroup column decrements by the specified amount.