How can a SQL Server Table get bigger when you want it to get smaller?

How can a Table increase in size when you reduce the size of a column?
This came up again a few days ago as one of my Developer friends took heed of my comments about using “appropriate Data Types” for Data Storage. They worked out that using BIGINT to store a Client number was probably excessive considering the nature of our business. We would need an explosive growth to get into the thousands, let alone the sort of values that can be stored in a BIGINT column.

I digress, the point of the matter is that they decided to use ALTER COLUMN to change the current column definition from BIGINT to SMALLINT and then complained because the Table almost doubled in size.

This phenomenon is due to the way that SQL Server processes the request. First, it adds a new column of the required size (SMALLINT in this example), then it copies all of the data from the BIGINT version of the column. It leaves the old column in the Table, taking even more space!
Luckilly, there’s an easy fix – you can instruct SQL Server to rebuild the Table. During this process, the Table will be compacted.
ALTER TABLE [TableName] REBUILD

There are a few caveats though:
This operation is fully Logged, so the Database Transaction Log needs to be large enough to hold the newly-compacted Table while it’s being built, and before the original is removed

This might involve additional work if you’re already out of Disk Space. I’ll cover that in another Post.