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
Pictures in Database.
1) Is there any reduction in the image quality of the picture?
2) If the database crashes (hopefully there is a backup) do you loose thousands of pictures?
3) Which loads into a web page faster? The DB picture or the file system picture? I would think the DB picture.
4) If the pictures are not in the database you can easily review them, edit them, and update them if you need to without digging into the database. This can be a plus, or as said, a minus for medical or legal pictures. It could be a mess if the relation to the database links is not clearlly understood.
5) If somone needs a copy of the picture, say on flash drive, how easy is it to extract it and provide that?
As I read above, true and must be concern for “It depends of your company strategy and direction”. We lived for almost 8 years with, SQL for main human resource information of “30 GB” size, with 1T of image with average 150k on file system. We hardly face problem. linked with the “path name”, “filenumber”, File Name”. Because some time the ID change to new one, so we must have different number for it in our database, with new path link in his record.
First, we start it fine, but now as I see the same person come back after a while with new picture and information to add in our system. New scan added to last point in our file system. Whish after a while we start to have defragmentation process for file system to keep all the image together.
But after reading here, I Liked the Ida of having the second Database, which it can be index, Linked with first SQL, Cluster, and a lot of easy to recover for DR, redundancy, backup, even in the future if we feel in performance problem, we can easily add more server for load balance, with share hard disk like SAN, or IBM Shark.
Also, I see in important point of a lot of IO in file system, which is most of the time killing the system or wait a lot more second to get in process. But in SQL, in the future you can have a lot of backup for yourself to play with or even to expand, like adding extra server for load balancing, fail over and a lot more, which a lose don’t put you in bad shape as a designer or responsible for the project .
From the application side, once the employee enter ID, automatically he get in the back process to “his PC cash” five to ten image arrived. And as SQL, or DB2, you can have a lot of option to play with as increasing hardware like, cash memory, increase number of server, a lot of tools to have your data also in cash level and so on. With file system, you will see yourself a lot restricted, and not mush to do.
Regard…
I am not disputing about storing images in the filesytem and store rest of the file info in the database. I have seen so many links that teach you from the scratch in different logic than my code. but I am trying to fit only this binaryfile blocks into my code logic. Though my destination is to get to display the directory path in the browser, I think I have to rephrase my question to avoid confusion at this point.
I only want this single question answered please.
Could you store directory path in a string variable and display that value in the browser through any other webcontrols or directly? If yes please show me how did you do it?
Thanks
Ama
Just run a SELECT * on your table with images and then run a select without the blob field and you will see the performance difference.
IMO…
SQL Server 2008 has a option wich alocate phisically (file system) the file stored in a field, this does not stay together the “normal” content row.
The ACID properties of transactions are difficult and complex to maintain if you use a file.
You may want to write to a temporary file and rename the file on successful transaction.
Moreover, optimizations to database design and query style can be more helpful.
As suggested above you can use another table to store the images, and retrieve the image only when necessary. Thereby, reducing the load on database server.
//— You can store the file location into the database using the below code. –//
//– you need to have a FileUpload control in your page to upload the images –//
string myFileName = FileUpload1.PostedFile.FileName;
string copy = Path.GetFileName(myFileName);
try
{
string Upath = @”~\Images\” + copy; //– This is the server path where the images are stored–//
FileUpload1.PostedFile.SaveAs(@”C:\Images\” + copy); //– This is the same server path in file system using file path instead of root directory. This is just for an example –//
string sql = “INSERT INTO Frm_ATAC_Events (Content_Type, File_Path) VALUES (‘” + FileUpload1.PostedFile.ContentType + “‘, ‘” + Upath + “‘);”;
SqlConnection connection = ConnectionManager.GetAetnaForumDBConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
//– You can use the below code to retrieve the images back to a grid view–//
DataSet ds=new DataSet();
string sql = “SELECT FILE_PATH, CONTENT_TYPE FROM Image_Table”;
SqlConnection connection = ConnectionManager.GetAetnaForumDBConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds, “Images”);
GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
//— Now Bind the ImageUrl in the gridview to the File_path field using the code ‘Eavl(“File_Path”)’.–//
This should work very well…
I have the code for storing image url into database but not able to retrieve image using the url. Instead of retrieving image when checked it retrieves url but with “\\” in the path
I think the size of a Data Base is not Important anyway. Because today’s DBMS’s are very Powerful (such as SQL Server 2008).
But its better you use two or more File-group For a Data Base.
You can determine each table goes to a different File-Group and even you can determine each File-Group save in a different Drive
example:
Table1 save to File-Group1 And File-Group1 Save in Drive C
Table2 save to File-Group2 And File-Group2 Save in Drive D Of Hard Disc2
Today’s if someone say: ” it’s not correct to save the images to the database because the database will huge ” Like someone say : ” it’s Not correct to save the images to the Hard Disc!”
how can we storage image in database and how to retrieving it in sql command prompt.
The simple and effecient method is to store only path of the file in a column and store actual images in the server’s directory.
Could any one tell me how can I export the IMAGE or any other FILE which is inserted in database?
how to retrieve it in sql command prompt?
I think it depends on how well the indexing system of the DBM works. When you only store the filepath/filename in the database you practically handle the indexing yourself. That at least in theory should be faster unless you cram thousands of small files into one directory — more than 2000-2500 files and you will move at a snail pace — try placing that many files in a directory and then try accessing it – it will take forever. So depending on how you arrange those files it could even be slower.
I work on a daily basis with a 400K+ records IBM DB2 table containing 16kb images and access time is usually under 1s so it’s pretty good. And that’s an old DBM version(7.2) running on an old IBM OS2 operating system inside a virtual machine so there is much room for improvements.
I think every DBM has caching mechanisms that work quite good so we shouldn’t be afraid of using them. As I said storing only the filename should be faster but only if you spend the time on optimizing the directory structure and for me that is not worth it. Just let the DBM handle it.
Of course for databases with tens of millions of records things could be different and performance issues could force someone to reconsider.
Hi Pinal,
Everywhere its said not to use image /BLOB data types. But when the database have those columns and which is unavoidable then what we can do. I need to replicate a table and this table take 5hrs.. to replicate. Can you suggest how to increase the performance for replicate this table.
Thanks in advance.
Good day very cool blog!! Man .. Excellent .. Amazing .. I will bookmark your site and take the feeds also?I’m glad to search out so many helpful information here in the submit, we’d like develop extra techniques on this regard, thanks for sharing. . . . . .
i want to store image database
Comments On SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)
I have read your article and while creating the data base I have stored the urls of image in the database .When I retrieve the images by the Product,aspx using a HTTP handler ShowImage.aspx all the images are displayed . But the problem is that when I click on the image button for looking the product details of a particular product then the details of all the products are displayed . I need the details of that product which has been clicked by the Image button The pages of coding I am pasting here
Show me the way please
Sincerely Yours
Upendra
Database fields
p_id int Unchecked (Identity)
p_name varchar(50) Checked (storing the urls of images)
brand varchar(50) Checked (brand name of the product)
p_file image Checked (storing the image)
price int Checked (price of the product)
description text Checked (Descriptin of the product)
Product.aspx
Untitled Page
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="”
SelectCommand=”SELECT [p_id],[p_name], [brand], [p_file], [price] FROM [mobile]”>
<asp:ImageButton ID="ImageButton2" runat="server" ImageUrl='’ Height=”150px” Width=”150px” PostBackUrl=” >
/ItemTemplate>
ProductDetails.aspx
Untitled Page
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="”
SelectCommand=”SELECT [p_id], [p_name], [brand], [price], [description] FROM [mobile]”>
<asp:Image ID="Image2" runat="server"
ImageUrl='’ />
description:
<asp:Label ID="descriptionLabel" runat="server"
Text='’ />
ShowImage.ashx
Untitled Page
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="”
SelectCommand=”SELECT [p_id], [p_name], [brand], [price], [description] FROM [mobile]”>
<asp:Image ID="Image2" runat="server"
ImageUrl='’ />
description:
<asp:Label ID="descriptionLabel" runat="server"
Text='’ />
I have the code for storing image url into database but not able to retrieve image using the url. Instead of retrieving image when checked it retrieves url but with “\\” in the path
Hi. I am very familear with the pro and cons of saving the images in the DB vs just saving the link to the file. If the changes to the file needs to be tracked – save not only the link to the file in the DB but also last save date, file size and last changed by, You are then able to tell if something or somebody changed the image file. Enjoy ;).
This article should help put an end to most of the discussion herein:
Note: Some of the comments in this blog describe people who have images and/or BLOB data in their databases. Every effort should be made to change that if you are such a victim. I have worked with extremely large data systems for the medical insurance industry and take my word for it, the impact on your database performance will become a major problem if you include binary data within your database schema, no matter how effective your server systems may become. And do not assume that your database will ever become so big as to have such problems. One horrible case I had to help fix was a database system built back in 1998. It was originally expected to support just a small contract. The contract was so successful that the data owners expanded their business well beyond the original requirements of the database system. This was AFTER the original developers had moved on to other projects or companies. So there was nobody to stop them from adding more data to a system not spec’d for such volume. The result was a slow but steady decline in database performance and backup operations effectiveness.
The filestream solution in SQL Server 2008 is a new and safer way to deal with the problem. Learn it and you will feel much better about your database systems.
Keep up the good relational fight out there!! :-)
hey ..u tell us dat store images url in database…its easy but wat if i have to store thousands of images under single id….. then????
Thanks for all the posts above. I have a situation where I’m building a fingerprint software that stores the fingerprints and employee images in a database.
I think storing in the database makes it easy for me to backup and restore and replicate data compared to storing in the file system.
please help me.I’m interships student that need to do a task about image which i need to navigate the image drive directory path(C:\Source),check all data in image directory file path exist or not match with student id that exist in existing database,if exists move to others drive directory path(C:\Destination) then delete data image from (C:\Source),if not exists move image to the(C:\Dump) then delete also from previous image image directory path.