Just a day ago I received phone call from my friend in Bangalore. He asked me
What do I think of storing images in database and what kind of datatype he should use?
I have very strong opinion about this issue.
I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
Yes, it is very much easier to manage the image’s path than the image. But, how can we secure the image from being not accessible without the usage of database. Is that the responsibility of the administrator to protect those images?
You can ask your Network Administrator to hide them from viewing from folder
There are situations where storing images in the database, alongside the data they concern, is an excellent idea.
For example, in the medical field, it’s very frequent that an image and its metadata are a single logical blob.
You don’t want to have “pointers” to images that can be altered or removed behind your back. You don’t want users to add or delete medical information outside transactions. You don’t want to possibly deal with two different security systems (one for the database, one for the file system). You don’t want to split backup or fall back strategies.
Nothing prevents you to link two separate databases and to keep blobs even on a separate server (DTC is your friend), one for data and one for images. That’s what we do in our application (400GB of data, without images).
As you mentioned yourself, DTC is your friend. You can encompass SQL Server Changes and File/IO changes inside a single transaction.
David Brabant, I thought your tip very good. I have no idea how to do that. I want to learn how to link two separate databases and to keep blobs on a separate server one for data and one for images.
I’m developing a web system using C# and ADO.NET and it would be great if I could develop in this way.
Do you get some article teaching this? If not, do you have some short sample project using this to share with me?
Thank you
Gladson Pereira
Hey,
I used to think that storing the binary files in the file system and the links in the DB columns is the way to go, but there are times when you want more reliability, you don’t want someone changing the file names or moving them somewhere else.
Was just wondering on the performance implication of storing the binary files in a DB versus storing them in a file system. you have any clue about this?
Yes its gr8 idea , i was using that technique for single desktop applications. at present m working on a multi-user distributed application ( centrilized system). And m facing problem with this techinique because the Remote Location is password protected ( the pc where databse is installed ) , and if i do provide access to that Machine , it will allow to access the resources of that computer , will u plz suggest me the settings by which i can restrict users to access resources of that computer and and can allow only reading images from a particular picture folder( contains images )………..plz guide me
I am new in this field
You suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using dreamweaver VB scripting with asp.net to developing the sites.
Thanks
Refer these
https://docs.microsoft.com/en-us/previous-versions//cc917636(v=technet.10)
https://support.microsoft.com/en-us/help/317016/
I been trying to get the answer for this for long time. I dont’ know either, how to store the filepath in the database. Did you solve this issue. Pleaes let me know if you know how to store. Atleast I would like to see it displayed in a lable control or something if not in DB. Cuz I assigned a directory path using “~/picture/ samples ” to a string variable, but when I tried to dispaly this string variable in a lable control I dont’ see any value at all. Can anyone answer me why or tell me how to get it displayed?
thanks
Ama
Hello,
the implementation depends on what technology you use for the middle tier, but the logic for the images is very similar: the players in your game are Web server, database server and middle tier (.NET programming language, lets say which commands the database and serves the Web server.)
So, you save the physical images on the web server (well, in an ideal world you have a media server), then in the web.config in the middle tier you save the first part of the path to the image, i.e. the directory path which is common for the images. Then in the database you write only the picture unique path and the picture file name. (And you will need a imageID or something to distinguish it from the other images when you search for it in the database table). Now, when you need an image displayed on your website, you will have a control (some kind of encapsulated logic) to which you will pass the imageID and the control will go to the database, get the image info, concatenate it with the information from the web.config, and voila – you have a image path which works. I am sure that there are plenty of examples on the web.
you can use the image data type instead of string var
for eg. Image img=”src path”
and pass this img to the label
can you please demonstrate on how to do that. I need to store file path to different types of files such as pdf, doc, audio, video, etc. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
emilia
The challenge behind the directory structure is security and if you’re in a SOX environment its hard for the developer to have all of the tools to properly configure the file server, the web server, etc. all in the name of what? Performance?
If performance weren’t the kicker, the database seems like the way to go (assuming its sql 2k5, which argues that its faster than oracle up to the TB). With the DB I know where my files are and I have one less thing I need to do…(assuming your files do not change; such as imaging files).
Therefore, an arguement shouldn’t be made w/o some kind of performance justification…so please would the smart guy in the room tell me how much faster a file server is versus a database server? If its nominal the answer should be what’s the least expense for your organization to maintain? In a SOX cause, I would argue the db approach…
Storing images on the database, consumes the available capacity of the database files.
For example, if you have 2000 medical records, with ten 5MB images for each record (a medical record could have more than 50 images, but I just chose a small number), then 10GB of the database available size is consumed for the images.
For larger databases, that could be a problem.
Correction for the previous post:
It’s 100GB, not 10GB.
can you please demonstrate how to store the location (URL) in table. I have never done it and not sure where to even start on that. i would greatly appreciate it.
thanks
I am trying to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.
How I can do this ?
I am using visual studio 2005 VB.net with asp.net to developing the sites.please guide me how to solve.it’s urgent
Just pass the file path as the column value in the SELECT statement and make sure the files are located in Server’s directory
You might want to create two fields, one for the path, one for the filename. As an example, store ‘patient\images\’ as the path, and ‘Patientimage1.jpg’ as the image name.
Instead, you could store the path in another table if it is the same for all images, or images of a certain type. That way you could save different paths for different types of files, such as .PDF or .GIF.
I like the idea of storing in the database, as this keeps the system from having orphan images, and keeps backups and restores current (keeping records and images together).
I would have to disagree with the author about a one size fits all of storing images on the file system rather than in the database. It all comes down to my favorite answer, “It depends!”
How about this senario, a shop that has over 20 million images stored on a file system, each of the images are under 1 Mb in size. If you look at it, strictly from a DBA’s prespective, then yes, storing files on the file system and having the pointers stored in the DB sounds reasonable. Unfortunatly, we live in the real world here and it you looked at it from a backup and recovery standpoint, how long would it take a backup system to backup over 20 million small files. Many, many times longer than it would take to backup one large file.
Bottom line, is before you make a descion to go one way or the other, make sure you have factored in ALL options.
Marc –
For your situation, it is acutally recommended to store those images in SQL Sever and not on the file system. SQL Server processes files under 1 MB at a rate of 2x’s faster than that of NTFS. So in your case, it would actually be a benefit to place these images on the server. In contrast, it is not recommended to store files over that 1 MB threshold as you then start to get into potential performance issues and fragmentation of the database.
I’m working on a project. Actually, the software is ment for Radiology department. The software suppose to keep all cinical information, and medical images. (e.g. Ultrasound image, CT, MRI )
I’m intend to use C# 2005 with MS SQL server 2008.
Please where do you suggest I should store the medical images of the patient?
in the database or on the file system.
Please I need help.
This is a hard decision to decide. What about maintaining the images. If you store in a file system, how do you delete them when you delete the reference from the DB? I want to store images for classified ads, and don’t want the images lingering around for years after the add has been gone. In my case all images
Also, mainstream sites that handles millions of pictures like cars.com seem to use a files sytem. They also seem to create a lot of new directories. From my understanding you don’t want to store more than 3000 images in any given directory or there will be a significant penalty.
They have a directory structure like so:
There are 3 different size images. The images sizes are 2k, 16k, and 50k respectively. The images load instantly. Do you think they can get that kind of performance from a DB?
sorry the last one was:
Also never finish the sentence “In my case all images..”
should say “In my case all images are probably less than 50k each.
I prefer to store in the DB. Space is not an argument. You will either have a larger DB or you will need a file server fo rlarge sites with a lot of photos for example. So its much cheaper tohave it in the DB.
The insert/update/select is by no means “massive” as the author says. You would have a memberid or some other indexed cilumn in which you are using to grab the row.
I think it is much nicer to not have to worry about a file server. The performance hit is marginal it is definately worth doing.
Plus it also allows you to stop using cffile and you cna use cfimage tags and functions to alter image to a blob and pas sit to your stored proc.
Its a win win all the way around really. I own a dating site with over 100k members and store all profile pictures in the DB. Each member can have up to 200 photos. When I display the gallery I do a next n and only show 10 at a time.
it is a wonderful site providing valueble information for my project
I would like to know which drives for the NTFS files system would be preferred.
We have a SAN with combonation SATA and FC-drives.
Can I still store the binary\photo files onto the SATA (15k) drives with HBA(4gb) and get good performance on the reads and writes? Very curious about this one? The system will produce fast transaction from mobile pda’s and about 6000 users will update within about 3hr period. Any other performance recommendation will be greatly appreciated. I have already sepreate all data files on seperat arrrays and give tempdb 6 fix length files.
Please comment at will.
I definitely agree with Pinal regarding the images in a database – it is a bad idea in general. The reason for this is scalability: over time you will eventually come to an IO bottleneck if you keep your images in a database. The you will try to purchase more and faster storage, then you will have to think about partitioning of your database, then about complicated backup strategies. On the other hand, if you keep the images on disk, there are many free tools (Microsoft Resource toolkit, for example) which will give you the opportunity to backup only the changed files, and eventually you will come to a point when you will need a standalone media server.
In SQL 2008 you can look into filestream, which changes things a bit.