I believe SQL Server would create a new column under the hood and copy the original data to the newly created column. And the original space for the old column cannot be reclaimed before the table is rebuilt.
But I can't come up with a general picture in my head about what exactly is going on.
Say I have a simple table defintion:
CREATE TABLE ExampleTbl ( ID INT ,Bool BIT )
And a certain page is completely filled with
ExampleTbl's data rows. What happens if I change
Bool column data type to
Since the page is full, does it mean the the newly created column will have to be stored in some other data page? I originally thought it should be in the same page because SQL Server will always try to use in-row allocation when the data row fits into a page(less then 8000 bytes).
But then it may involve a lot of moving around of the data rows to allocate space so the new column can be inserted into the data page, and some rows will have to be popped out the page and stored in another one, if SQL Server needs to stick to in-row allocation. But this behavior is obviously detrimental to system performance.
So exactly what happens behind the scenes when I change a column data type to one with a wider domain of values?