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?
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:
(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)
.