Advantages and Disadvantages of Storing Images in SQL Server
Even
Microsoft does not recommend storing images in SQL Server, because
this causes performance and database disk space issues. An application
that stores images in a SQL Server database will experience performance
problems each time an image is stored or retrieved from SQL Server
because of the way SQL Server needs to store or retrieve images.
SQL
Server needs to convert an image that is larger than 8K into multiple
chunks, and then store these chunks on separate SQL Server pages. When
SQL Server retrieves a large image stored in a database, the image must
be retrieved in chunks and converted back into an image. This process
of breaking up an image into chunks and reassembling these chunks back
into images is what causes performance problems. Also, storing images
in a database will make the database considerably larger, so backing up
and restoring the database will take longer.
Knowing
how often your application is going to insert, update, and select an
image from a database might help you to determine how greatly
performance will be degraded if you store your images in a SQL Server
database. If you are only inserting the image once, and retrieving it
rarely, then the performance hit will be minimal for each usage of an
image. On the other hand, if your application is serving up a single
image frequently, then the performance drain on SQL Server could be
significant. When the performance impact is significant, it is best to
just save the image natively in a file system and store only a pointer
(a URL or an actual file location ) to the file in SQL server.
There
are advantages to storing images in a SQL Server database, however.
One advantage is that it simplifies managing the images. If you want to
move your database to a different database server, it is as easy as
copying the database. Another advantage of storing the images in SQL
Server is the extra layer of security around the images. By storing
images in SQL Server, you can manage access to images using SQL Server
logins and roles. This extra layer of security makes it harder for an
individual to gain access to your application images.
