locked
"Storing images in SQL 2008 R2 database Or Storing image in folder and save its path in database" - Which is better the most advisable? RRS feed

  • Question

  • User-582711651 posted

    Hi Experts, 

    Pls simply tell me which is better the most recommendable?

    Weather Storing images in SQL 2008 R2 database Or Storing image in the folder and save its path in the database?

    Thanks in advance.

    Wednesday, September 26, 2018 5:06 AM

Answers

  • User-369506445 posted

    hi

    As usual, it depends

    Storing images in the database:

    PROS

    • If the images are to be associated with entities in your database (say, a user), the database can take care of maintaining that relationship. If, on the other hand, images aren't associated <g class="gr_ gr_37 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="37" data-gr-id="37">to</g> anything in the database, you will probably not want to store them in the database.
    • If your database supports it, you will be able to process files within a transaction (I believe MS SQL 2008 supports this, I don't know if others do).
    • If you need to store multiple versions of each image (say, because they change over time), it will probably be easier to do in the database than on the file system.

    CONS

    • You will be putting a lot of strain on the database.
    • Backing up your database may take a long time.

    Storing images on disk:

    PROS

    • Making backups is trivial
    • Inspecting images etc. just requires a file browser, no need for a database client

    CONS

    • Keeping the database's view of the image collection and the actual content on the disk in sync may be non-trivial, depending on the operations you will be performing on the images.

    Of course, all these concerns are particularly valid if you store large numbers of images.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 26, 2018 5:26 AM
  • User1724605321 posted

    HI ayyappan.CNN,

    That depends on your requirement and priorities . In short store image into database is easier to back up ,across the systems and independent on file system and more secure , store in file system helps save space on server's DB storage and is easy to retrieve/reference in your application . 

    You may check the FILESTREAM (Depends on file size , see link below) which has the advantage of providing transparent transactions while still storing large files on the file system:

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb933993(v=sql.105) 

    FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. 

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2018 6:09 AM

All replies

  • User-369506445 posted

    hi

    As usual, it depends

    Storing images in the database:

    PROS

    • If the images are to be associated with entities in your database (say, a user), the database can take care of maintaining that relationship. If, on the other hand, images aren't associated <g class="gr_ gr_37 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="37" data-gr-id="37">to</g> anything in the database, you will probably not want to store them in the database.
    • If your database supports it, you will be able to process files within a transaction (I believe MS SQL 2008 supports this, I don't know if others do).
    • If you need to store multiple versions of each image (say, because they change over time), it will probably be easier to do in the database than on the file system.

    CONS

    • You will be putting a lot of strain on the database.
    • Backing up your database may take a long time.

    Storing images on disk:

    PROS

    • Making backups is trivial
    • Inspecting images etc. just requires a file browser, no need for a database client

    CONS

    • Keeping the database's view of the image collection and the actual content on the disk in sync may be non-trivial, depending on the operations you will be performing on the images.

    Of course, all these concerns are particularly valid if you store large numbers of images.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 26, 2018 5:26 AM
  • User1724605321 posted

    HI ayyappan.CNN,

    That depends on your requirement and priorities . In short store image into database is easier to back up ,across the systems and independent on file system and more secure , store in file system helps save space on server's DB storage and is easy to retrieve/reference in your application . 

    You may check the FILESTREAM (Depends on file size , see link below) which has the advantage of providing transparent transactions while still storing large files on the file system:

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb933993(v=sql.105) 

    FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. 

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2018 6:09 AM