How to free space after I drop a column of a table in SQL Server? The background is that there is a column Fn in a table DataProcessed in SQL Server. Plus, the property of Fn is varchar(max), NOT NULL.
The problem is that I hope to drop Fn from DataProcessed, and free the space which is occupied by Fn in the NDF files of DataProcessed.
How can I finish that? Or, is this doable?
You can run a shrink on your database or its individual files, but in general it's not recommended to do because it's a heavy operation and generally wasted efforts because the space will likely be re-used by your database anyway which will result in a growth operation which is also not a lightweight operation.
When you drop the column, the space it was previously using on disk will be recycled and re-used by your database as it needs more space anyway. It isn't just wasted space just because the OS shows it consumed from the disk. SQL Server manages the space it reserves from the disk internally for the very reason of reducing those heavy growth operations I mentioned earlier. So no need to worry about shrinking.
You can also find more good information on why shrinking is a wasteful operation here.
Also, last thing to note is that a VARCHAR(MAX) column doesn't consume the entire ~2 GB it's capable of maxing out at, for each row, unless you were filling it to the max. It's approximately equal to 1 byte per character you're actually using in it, in general (not counting any additional space saving features going on under the hood like compression). So the space savings you might anticipate by dropping it is potentially less than you think.