image vs varbinary(max)

• noumanjee

  SSC Veteran

  Points: 245

  More actions

  September 22, 2010 at 5:44 pm

  #142845

  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).

  Question:

  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.

  Thanks,

• Oliiii

  SSCertifiable

  Points: 5328

  More actions

  September 24, 2010 at 3:50 am

  #1226798

  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).