Changing a column from
int
tobigint
has 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 doALTER TABLE...ALTER COLUMN
as an online operation using theWITH (ONLINE=ON)
syntax. This wonderful syntax now allows you to alter a column fromint
tobigint
without causing major blocking. Theint
tobigint
conversion 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.