Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

image vs varbinary(max) Expand / Collapse
Posted Wednesday, September 22, 2010 5:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 26, 2011 12:02 PM
Points: 1, Visits: 9
I maintain an application which was developed back in 2000 and it was developed against SQLServer 2000. Last year we migrated the Database to SQLServer 2005. The Main table that our application uses has a column with image datatype to store large objects (blobs).

1. I was told that that SQLServer 2005 has better way of handling the binary objects i.e. varbinary(max) and that I should consider migrating to varbinary(max) from image datatype. I was also told that with varbinray(max) i can reclaim the space if the large objects are deleted from the DB.

2. I cannot reclaim space enough space after i purge lot of data from the table that contains image datatypes. I have tried re indexing as well as DB shrink but i cannot reclaim more than 10%.

FYI I do the following to reclaim all true space.
a. Transfer all the objects from the main table to the template table.
b. I drop and recreate the Main Table.
c. I transfer the objects back form temp table to the main table.

I hope someone could comment on this.

Post #991655
Posted Friday, September 24, 2010 3:50 AM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 8, 2015 7:12 AM
Points: 206, Visits: 777
The same thing happen with text field, switching to varchar(max) fixed the problem.
I guess if you switch to varbinay(max) your problem will also be fixed (you still need to reindex to get the space back though).
Post #992591
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse