CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Karan
Karan

Reputation: 73

In SQL server, does setting all values for a Text column to NULL reduce database size?

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

Answers (3)

Romil Kumar Jain
Romil Kumar Jain

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

JNK
JNK

Reputation: 65217

To actually reduce the DATABASE size, you need to:

  • Drop the column
  • Rebuild the clustered index or the table if there is no clustered index
  • Shrink the database files

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

Jon Crowell
Jon Crowell

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

Related Questions