SQL Server: Storing Images and Photos

  • Article

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