Ed Pollack goes big or goes home:
One of the most hassle-prone changes that can be made in a SQL Server database is to widen an existing column. Depending on the column’s use and data type, this process may involve hundreds of schema changes, app downtime, and a mountain of risk-prone code to review and deploy.
This article dives into a fun (and interesting!) strategy for widening fixed-width columns in SQL Server, to reduce downtime, risk, and runtime at the time when a column’s data type needs to be changed.
If you have ever suffered through a lengthy INT to BIGINT release, then this guide is for you!
Read on for a step by step guide on expanding a column with minimum downtime, as well as a quick description of row- and page-level compression and how you can use those to ensure you’re using as few bytes as necessary when storing a smaller number in a larger data type.