Storing images in SQL Server?

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 mistake less if I could.

storing images in sql server

I am trying to put together db design for storing images. Many of you might have had experience designing db to store images and the challenges associated with it. The db might store hundreds of thou

Problem with storing images in sql server

I am trying to store images in sql server with data type image now the problem is getting stored into two rows, i am using FileUpload control to upload image, my code is as follows byte[] imagedata =

SQL Server Table/Index for storing Images

I am having some problems with storing Images in our database. We currently have about 400k records but I expect this to increase into the millions quite quickly. At the moment I am already experienci

Storing images in SQL Server using C# [closed]

I want to store images of my employees with their profile details in SQL in a SQL Server database, but I have following reservations: Should I compress images or not and if yes, please can the communi

SQL Server table structure for storing a large number of images

What’s the best practice for storing a large amount of image data in SQL Server 2008? I’m expecting to store around 50,000 images using approx 5 gigs of storage space. Currently I’m doing this using a

Storing and retrieving images in sql server 2005 in asp.net via c#

How do I store and retrieve images in sql server 2005 in asp.net with c#

storing passwords in SQL Server

What is the recommended practice for storing user passwords in SQL Server 2008? I am storing user details for an intranet, and would like to get advice on best way to store a user details such as name

Which data-type for storing images and documents in SQL Server 2005

I must extend an existing .net-Application to store images and documents (mostly pdf’s) in a SQL Server 2005 database. Which SQL Server datatype is used best for saving these files and is it meaningf

Storing and retrieving images with MVC and Sql Server

I am designing an prototyping an app the needs to store images, similar to facebook. This will be a public facing site and I am not sure how many users I will end up with but what I am looking for is

Storing .dll files in SQL Server 2005

Where can I find information on storing .dll files in SQL Server 2005? Is it the same as storing Binary data?

Answers

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

However if you do store the image in the db. 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

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!

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

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.

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

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

When you store your images table at a different disk or physical area it makes your system even faster. It’s a very strategical and well knows solution.

Also you can manage your images well. For instance: You can tag your images. It depends on your purpose. I use images to match foreign words. Some times I match 1 image more then 5 words using image tags. It keeps table smaller.

Also there are tons of image processors library to make your images size lower. You may want to look at that library: http://imageprocessor.org/ My 400×400 images has 20 – 40 kb sizes with 50% quality of image.

And like everybody says you can easily take backups.

There are not many situations where I would recommend storing anything but text in a database. Sure, you can store files in a Sql Server database, but that doesn’t mean you should.

If you care about performance and maintainability, then store the paths to the images and not vice-versa. Disk space might be cheap, but if you’re storing a lot of files then you will soon fill up a lot of space and it won’t be pretty, especially when your queries become so slow your application takes literally seconds to complete.