sql-serverlarge-data-volumes

SQL Server table structure for storing a large number of images


What's the best practice for storing a large amount of image data in SQL Server 2008? I'm expecting to store around 50,000 images using approx 5 gigs of storage space. Currently I'm doing this using a single table with the columns:

ID: int/PK/identity
Picture: Image
Thumbnail: Image
UploadDate: DateTime

I'm concerned because at around 10% of my expected total capacity it seems like inserts are taking a long time. A typical image is around 20k - 30k. Is there a better logical structure to store this data? Or do I need to look into clustering or some other IT solution to accommodate the data load?


Solution

  • Image is a deprecated data type in SQL Server 2008. It has been replaced with VARBINARY(MAX) since SQL Server 2005. If you decide to store the image in the DB, then you should use VARBINARY(MAX) fields and consider adding the FILESTREAM option.

    For streaming data, like images, FILESTREAM is much faster than VARBINARY(MAX) alone, according to this white paper:

    Filestream vs. varbinary(max) performance
    (source: microsoft.com)

    Note that to achieve this streaming performance you must use the proper API in your design and obtain the Win32 handle of the BLOB. Note that updates into a FILESTREAM column (including INSERTS) will be slower than VARBINARY(MAX).