Quick Tip: Decrementing IDENTITY Column

Did you know that IDENTITY columns can count downward?! According to MSDN, IDENTITY’s second argument, named increment, “is the incremental value that is added to the identity value of the previous row that was loaded.” No constraint is given that increment must be positive (remember from Algebra that both positive and negative numbers can be added). Set increment to a negative number and IDENTITY will generate a descending sequence of numbers.

IDENTITY(1,1) produces 1, 2, 3, …
IDENTITY(5000, -1) produces 5000, 4999, 4998, …
IDENTITY(1, -1) produces 1, 0, -1, …

Granted, using IDENTITY to count down is atypical. I’ve never done it in production and can’t think of very many reasons why I’d need to. However, the fact that it’s possible just might come in handy one day in solving some obscure problem.

Leave a Reply

Your email address will not be published. Required fields are marked *