February 13, 2013 at 10:59 am
I have a table which has an image column that contains an average of 67,000 bytes/row with 32,000 rows giving a total of 2100 MB of data for that column.
I ran an update to set this column to null for rows older than 3 months old. The total bytes stored is now 884 MB for that column.
However, the total space allocated to this table was only reduced from 2.6 GB to 2.4 GB, even after I rebuilt the table.
I tried running DBCC CLEANTABLE, and that had no impact, as I expected, since I did not drop the column.
The indexes are not that large, and there are no indexes that include LOB columns.
If I copy the data from that table into a new table using SELECT INTO, the size of the new table is 1.1 GB, instead of the 2.4 GB in the current table.
Reloading the table is not an option, since I expect to perform a weekly or monthly purge to keep the size of this table under control, and this is a 24x7 web application.
Is there anything that I can do to reduce the data allocation to bring it in line with the amount of data stored?
Would SQL Server handle the deallocation better if I change the datatype of the column to varbinary(MAX)?
The version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 Enterprise Edition (64-bit)
February 14, 2013 at 1:46 am
Hi,
You should change the data type from image to varbinary(max). Image LOB data is stored off-row (complex pointing), and varbinary(max) is stored in-row (part of the data and index)
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply