If I have a table with an NVARCHAR(MAX)
column and I drop it, the standard way to reclaim the space it used is to either:
- Rebuild the clustered index or
- Run
DBCC CLEANTABLE
If I didn't perform either of these, the column still remains (visible
via sys.system_internals_partition_columns
) in the physical structure of the table, and did a full backup & restore of the database, would the restored copy still have dropped column in its physical structure and take up the same amount of space as before I had dropped the column?
For my own use case I am looking at SQL Server 2012, but I presume this functions the same across all relatively modern versions. I am aware this is easy to test, but I am hoping someone has more background knowlegde on this as I haven't been able to find much myself.
References:
Best Answer
I wrote about how you can use DBCC CLEANTABLE to reclaim the space.
Under the hood, sql server just deallocates the pages and then a background thread called ghost clean up will clean up the data.
Another aspect that I am assuming you are asking is physical data security - how can I be sure that my data is not recoverable once I delete/drop it ?
To answer that, SQL Server provides -
sp_clean_db_free_space
Now that being said, its really hard to destroy the data esp. for drop column
Michael J. Swart has a very interesting post on It’s Hard To Destroy Data - esp the summary table with different options.