Richard O’Riordan runs into an oddity:
We have a wide table in MS SQL Server (2019), say 150 columns. We want to add a new bit column at the end of this table with a default value of 0, at first look at this I would have thought, that’s not a big deal, this is a meta-data only change but it wasn’t, the entire table got re-written, so here are some scenarios I tested.
This is quite odd because my recollection is that, with a default constraint, SQL Server shouldn’t need to write anything to the table, and that’s how it’s looked in my testing. Remus Rusanu also had a nice post getting into the details of how this changed for SQL Server 2012. Maybe it’s an oddity from having so many columns on the table or records of a certain width or something?