SQL Server: Storing Images and Photos
Real life Scenario
I'm currently reviewing and strengthening my SQL Server skills when I came into this dilemma. For a web based application, would it be better to store images in SQL Server or would it be better to store the images in the file system and just store the "file path" or the link (e.g." C:\images\image1.jpg") to SQL server
Why should I bother? It's the client call anyway? But as a matter of principle which is the right way to go.
Images and photos are Binary large objects to begin with. For reference see the link below.
http://en.wikipedia.org/wiki/Binary_large_object
Advantage of Storing Images in the file system.
This is of great advantage for applications that have:
- Small and simple logic
- Images that are static and doesn’t change very often
- No concern for security
- Extremely Large images such as maps
Advantage of Storing the Image in SQL Server.
This is of great advantage for applications that are:
Very Large application such as a HR system of with more than 200,000 employees with very fast movement of such as rehire and resignation
Highly sensitive image data such as medical x-rays or court evidence where security is extremely important
High availability requirements.
Maintainability requirements.
Designing Application that store images in SQL Server.
The right way of doing this is to store the image in a separate table and put it on a separate File group.
For more information on file group please consult this link:
http://msdn.microsoft.com/en-us/library/ms179316.aspx
The reason behind is that binary large object ( BLOB) may take more than a page to store data.
A “sql data row” is stored sequentially in a page. Storing the BLOB together with text data
might cause database fragmentation.For more information on pages and extent you can refer to this link:
http://msdn.microsoft.com/en-us/library/ms190969.aspx
Figure 1. database architecture for storing images
Data Types For Images.
For storing images you have to make use of the varbinary(MAX) datatype. The image datatype will
soon be deprecated
Getting the best of both worlds with FileStream.
Filestream storage was introduced in SQL Server 2008.
Varbinary(max) can only store images with a maximum size of 2 GB.
With Filestream you can store images larger than 2 GB.
A reference on filestream can be found on this link :
http://technet.microsoft.com/en-us/library/bb933993.aspx
Loading and Reading Images To and From SQL Server
The following link below contains code on how to load Images to SQL server..
http://www.codeproject.com/KB/database/ImageSaveInDataBase.aspx