Work with SQL 2005
I have a table with 5 columns
ID - int Param - smallint Data1 - image Data2 - image Data3 - image
Image size can be only 20 bytes and can be 1 MB in size (yes, more than 8 thousand). Image size is not constant because it contains compressed data. Most BLOB dates range from 512 to 1.5 kB. There is a logical connection between Data1, Data2 and Data3, so they are stored together. The database, containing 3 years of historical data, has 66 million records.
In order to improve data storage and prepare the system for future versions of SQL. I want to change image columns to varbinary (MAX) data type. FILESTREAM will be another option, but I'm not sure if it would be more efficient to store ~ 200 million files.
After changing the images to varbinary (max), I noticed that the overall size of the table decreased (a little bit), but every time I add / remove data, it takes a lot more time to complete the process.
From what I understand, if the value of varbinary (max) is less than 8k, it is placed inside the table, and if it is more than 8k, it is placed somewhere else, and the pointer to this data is stored in the table. When using an image, the pointer is saved even if the image data is less than 8k.
As a rule, 20,000 records are added and deleted daily, so no data is added / deleted, and one choice is usually found on 10-200 consecutive records.
Is there a way to set the varbinary (max) data that will always be stored (regardless of size) outside the table, just as the image is saved?
Gilad
source share