Press "Enter" to skip to content

Dueling Sequences for Positive and Negative Numbers

Jose Manuel Jurado Diaz hears banjo music:

SQL Server’s INT data type, by design, provides a range from -2,147,483,648 to 2,147,483,647. But often, developers only utilize the positive range for primary keys and other identifier fields, effectively wasting half of its potential. What if we could harness this full range to temporarily extend the capacity of an INT column? In this article, we explore this idea in-depth.

I’m of two minds here. On the one hand, surrogate keys don’t have a meaning (by definition!), so it doesn’t really matter if that number is positive or negative. Also, including negative numbers makes sense when you expect the steady state table size to be above 2 billion but below 4 billion rows (assuming that you’re using an INT datatype), or you’ve found out that the steady state size is that big after the fact.

On the other hand, I don’t like having a caller define whether they want positive or negative values, as that now imbues meaning to the surrogate key, where positive keys mean one thing and negative keys mean another.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.