Storing images in SQL Server?

Question

I have made a small demo site and on it I am storing images within a image column on the sql server. A few questions I have are...

• Is this a bad idea?

• Will it affect performance on my site when it grows?

The alternative would be to store the image on disc and only store the reference to the image in the database. This must be a common dilemma many people have had. I'd welcome some advice and would actually be happy to make a less of a mistake if I could.

Answer 1

When storing images in SQL Server do not use the 'image' datatype, according to MS it is being phased out in new versions of SQL server. Use varbinary(max) instead

https://msdn.microsoft.com/en-us/library/ms187993.aspx

Answer 2

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 256K 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!

Answer 3

While performance issues are valid the real reasons in practice that you should avoid storing images in a database are for database management reasons. Your database will grow very rapidly and databases cost much more than simple file storage. Database backups and restores are much more expensive and time-consuming than file backup restores. In a pinch, you can restore a smaller database much more quickly than one bloated with images. Compare 1 TB of file storage on Azure to a 1 TB database and you'll see the vast difference in cost.

Answer 4

Another option was released in 2012 called File tables: https://msdn.microsoft.com/en-us/library/ff929144.aspx

Answer 5

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

Answer 6

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.