November 22, 2024
Read Time: ~12 minutes
<aside> 📌 At Empower, we seek individuals who challenge personal assumptions, value ownership and trust, and strive for excellence to inspire and empower their team. If this article connected with you, join our team!
</aside>
<aside>
📎 Running out of range on a primary key in SQL Server is a ticking time bomb, especially for high-traffic production tables. This article dives deep into the process of migrating a primary key column from INT to BIGINT, tackling challenges like constraints, clustered indexes, and data synchronization. Learn the tested strategies, performance tweaks, and lessons from a real-world scenario involving 650 million rows—ensuring scalability and reliability for your database. Perfect for engineers navigating similar database dilemmas.
</aside>
https://open.spotify.com/episode/2fzaLr6AbQ9wMCVWvA3grQ
My initial thoughts when presented with the above problem was, “Oh, no big deal, we’ll just update it, right? WRONG!
In SQL Server, the INT data type ranges from -2,147,483,648 to 2,147,483,647. When a primary key (PK) column with the IDENTITY(1,1) property approaches this upper limit, it poses a significant risk to database operations. This article details the challenges and solutions encountered during the migration of a PK column from INT to BIGINT in a high-traffic production environment.
The primary key uniquely identifies each row in a table. An IDENTITY(1,1) property ensures that each new row receives a sequential integer value starting from 1. However, SQL Server does not reuse identity values from deleted rows, leading to potential exhaustion of the INT range. In our case, the PK value had reached approximately 1.9 billion, with the table containing around 650 million rows.
Well, nothing good! Every single new insert will start to fail as the data type in the PK column can’t physically hold a number higher than the top of the range!