The Next Big(int) Thing

One of our systems here uses a bigint identity column as a database primary key – because we knew when we built it, back in 2010, that we were going to end up with more than 2,147,483,647 records.

Well, that happened at 12:02 today, and a couple of systems promptly failed – because, despite the underlying database being designed to handle 2^63 records, the POCOs that were being mapped to those classes were using a regular C# int to store the record ID, and so as soon as they got an ID from the database that's bigger than Int32.MaxValue, they blew up. Thanks to the underlying DB schema already supporting 64-bit IDs, the fix was pretty simple – just change int to long in a few carefully-selected places and redeploy the applications – but it's still annoying that something we knew about, and planned for, still came back to bite us. So I started thinking – how could we stop this happening?

The problem is that, despite being a bigint column, we just accepted SQL Server's default identity setting of (1,1) – i.e. start counting at 1, and increment by 1 each time. Which means that until you hit 2-billion-and-something records, it doesn't actually make any difference - and that takes a while. In our case, it took 5 years, 8 months and 26 days. During that time we've made hundreds of changes to our code, and in a handful of those cases, we've mapped that bigint ID onto a regular C# Int32 – and so inadvertently planted a little time-bomb in our production code. Tick, tick, tick…

So here's a nice neat solution, that I wish I'd thought of five years ago. Anytime you create a bigint identity, seed it with (2147483648, 1) – so that right from day one, it's already too big to fit in an Int32. Any system that tries to store an ID in a regular int variable will fail immediately, not in five years when someone creates that magic 2.14-billion-and-somethingth record. Even though you've effectively thrown away 2^32 possible values, you have another (2^64 - 2^32) values to play with, so you've lost a tiny, tiny fraction of the available keyspace in exchange for immediate feedback if any of your client apps can't cope with 64-bit ID values.