SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)

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 (http://blog.SQLAuthority.com)

About these ads

54 thoughts on “SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)

  1. 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?

    Like

  2. 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).

    Like

    • As you mentioned yourself, DTC is your friend. You can encompass SQL Server Changes and File/IO changes inside a single transaction.

      Like

    • 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

      Like

  3. 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?

    Like

  4. 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

    Like

  5. 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

    Like

      • 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

        Like

        • 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.

          Like

  6. 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

    Like

  7. 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…

    Like

  8. 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.

    Like

  9. 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

    Like

  10. 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

    Like

  11. 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).

    Like

  12. 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.

    Like

  13. 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.

    Like

  14. 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.

    Like

  15. 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?

    Like

    • 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.

      Like

  16. 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.

    Like

  17. 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.

    Like

  18. 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.

    Like

  19. 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?

    Like

  20. 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…

    Like

  21. 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

    Like

  22. 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.

    Like

  23. 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.

    Like

  24. //— 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…

    Like

    • 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

      Like

  25. 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!”

    Like

  26. 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?

    Like

  27. 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.

    Like

  28. 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.

    Like

  29. 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. . . . . .

    Like

  30. 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='’ />

    Like

    • 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

      Like

  31. 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 ;).

    Like

  32. This article should help put an end to most of the discussion herein:

    http://blogs.technet.com/b/dataplatforminsider/archive/2008/04/14/sql-server-2008-makes-it-easy-to-manage-blobs-and-files.aspx

    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!! :-)

    Like

  33. 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.

    Like

  34. 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.

    Like

  35. please ,how i can upload pdf file from client to wamp server then store the path of file ?? i use c# language by visual studio 2010

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s