SQL: Don't start identity columns or sequences with large negative values

This week, one of my colleagues was complaining that he had to do work on a database where every table had identity columns, and they all started at large negative values.

I remember some people promoting this years ago, to double the available numeric range for the data types, rather than just using positive values.

I didn’t like it when I saw it then, and I still don’t like it.

Let’s take a quick look at an example:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(-2147483647, 1) NOT NULL
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    CustomerName nvarchar(50) NOT NULL,
    CreditLimit decimal(18,2) NOT NULL
);
GO

INSERT dbo.Customers 
(
    CustomerName, CreditLimit
)
VALUES
(N'Johnny Nobody & Sons', 100000),
(N'Yet another company', 50000),
(N'Dodgy Brothers', 0);
GO

SELECT * FROM dbo.Customers;
GO

The output from that is shown in the main image above.

Yes, I understand that you can have 4,294,967,295 values instead of “only” 2,147,483,647 values. But what you’ve also done is make the table a pain in the neck for anyone trying to work on it. Machines aren’t the only audience for these numbers; humans need to work on them too.

There are freaks of nature who can remember numbers like this, but I’m certainly not one of them, and I’m not alone.

Worse, they had consistently applied this “technique” across the entire database, even on tables that only ever had a couple of hundred rows. So they’ve made maintenance on the tables much harder, for no reason at all.

Very few tables are going to have more than 2 billion rows, and if they do, just use a bigint. And as for starting bigint values at -9,223,372,036,854,775,808, just don’t.

And if you’re using sequences instead (like we mostly do), don’t do it there either. You also don’t need to squeeze the last value out and use zero either.

Finally, it’s important to consider that these large negative values don’t compress as well, so if you’re using at least row compression (which you should all be using at a minimum), the values are a problem for that too.

So look, please just don’t do this.

2020-04-16