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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In my experience  storing to the url to the images stored in another location is the best way for a simple project

User · Answer

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

[sql-server] Storing images in SQL Server?

Examples related to sql-server

Examples related to image