From Image to Varbinary(max)

Question

Working with SQL 2005

I have a table with 5 columns

ID - int 
Param - smallint 
Data1 - image 
Data2 - image
Data3 - image

The image size can be as little as 20 bytes and can be as big as 1MB (yes, more than 8k). The size of the image isn't constant as it holds compressed data. Most of the date BLOB is around 512B to 1.5kB. There is a logical relation between Data1, Data2 and Data3 that is the reason they are stored together. On a database that holds 3 years of history data, there are 66 million records.

In an effort to improve the data storage and get the system ready for future SQL versions. I want to change the image columns to varbinary(MAX) data type. FILESTREAM would be another possibility but I'm not sure it would be more efficient holding ~200 million files.

After changing the images to varbinary(max) I noticed that the overall size of the table has shrunk (just by a little) but every time I add/remove data it takes much longer for the process to complete.

From what I understand, if a varbinary(max) value is smaller than 8k it is placed inside the table while if it is bigger than 8k it is placed somewhere else and a pointer to that data is stored in the table. When using image, a pointer is stored even if the image data is less than 8k.

Generally, 20000 records are added and removed daily, so there isn't much adding/removing of data and a single select is usually on 10-200 consecutive records.

Is there a way to set varbinary(max) data to be stored always (regardless of size) outside of the table the same way image is stored?

Answer 1

You can use sp_tableoption to set "large value types out of row"

Some additional info