Reputation: 73
In the context of SQL Server 2008.
I'm working on compressing and migrating values from a column with Text
datatype to another one with Image
datatype, same table. For now, we don't want to drop the Text column. So, I'm just setting all the values of that column to NULL.
Does setting to NULL provide space gain right away? i.e. reduce database size?
Does dropping the column do that?
If not, what additional stuff needs to be done to claim that unused space?
Upvotes: 4
Views: 3138
Reputation: 20775
Text column takes minimum 16 char value for a column value in single row of table. To claim the free space reorganize data and index after setting all value to null in this column.
In order to reclaim this space we have to use DBCC CLEANTABLE or shrink the database.
Syntax: DBCC CLEANTABLE( Databasename, "Table_name or View_name")
Refer this link to defrag and reindex the table
Upvotes: 1
Reputation: 65217
To actually reduce the DATABASE size, you need to:
I do not recommend shrinking since it will cause fragmentation. However, the disk footprint of the database will not drop automatically. What you will have is a database with empty space inside it, which can be filled with more data/indexes without growing the database any further.
Upvotes: 1
Reputation: 22358
Setting the values to NULL won't make any difference. Dropping the columns will not free up the disk space on its own. You will also need to rebuild the index: Disk Space Taken By Dropped Columns
Upvotes: 0