Storing images in SQL Server?

Why it can be good to store pictures in the database an not in a catalog on the web server.

You have made an application with lots of pictures stored in a folder on the server, that the client has used for years.

Now they come to you. They server has been destroyed and they need to restore it on a new server. They have no access to the old server anymore. The only backup they have is the database backup.

You have of course the source and can simple deploy it to the new server, install SqlServer and restore the database. But now all the pictures are gone.

If you have saved the pictures in SqlServer everything will work as before.

Just my 2 cents.


I fell into this dilemma once, and researched quite a bit on google for opinions. What I found was that indeed many see saving images to disk better for larger images, while mySQL allows for easier access, specially from languages like PHP.

I found a similar question

MySQL BLOB vs File for Storing Small PNG Images?

My final verdict was that for things such as a profile picture, just a small square image that needs to be there per user, mySQL would be better than storing a bunch of thumbs in the hdd, while for photo albums and things like that, folders/image files are better.

Hope it helps


There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

  • if your pictures or document are typically below 256KB in size, storing them in a database VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

  • in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee photo in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee photo, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it "LARGE_DATA".

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!


I would prefer to store the image in a directory, then store a reference to the image file in the database.

However, if you do store the image in the database, you should partition your database so the image column resides in a separate file.

You can read more about using filegroups here http://msdn.microsoft.com/en-us/library/ms179316.aspx.

Tags:

Sql Server

Image

Recent Posts