Changing a column from
biginthas gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do
ALTER TABLE...ALTER COLUMNas an online operation using the
WITH (ONLINE=ON)syntax. This wonderful syntax now allows you to alter a column from
bigintwithout causing major blocking. The
bigintconversion is one of the most popular data type changes I see–A developer inevitably creates the table thinking they will never have more than 2 billion rows… then some years or months later 2 billion becomes a reality.
The DBA is left with the task of implementing that data type change, and now that it has almost 2 billion rows, it’s a well-established table and uptime during the change is a major consideration.
This is a great post from Andy. If you want to dig into the concept of near-zero downtime in more detail, I’ve got a series on the topic.