SQL Server vs Directory – Best Practices for Storing and Retrieving Images

asp.net-mvc-3image manipulationsql server

I am working on a project in Asp.net MVC and need to work with images. There is an SQL database with a Product table. Every product in the table will have it's own image. I have two ways to do this :

1) Save the image in a web directory and store the URL on database.

2) Store the image in SQL itself in binary format and then retrieve it.

Which is a better approach ? Mind you, I have no idea how second method works 😛 . I will only learn this if there are merits to the second method

Best Answer

I prefer to put binary in the database, but that's primarily because

  • I know where it is, and I don't need a systems guy to set up a network share or webserver.
  • I can control who can retrieve the data through my application, rather than rely on permissions on the file system. I can do it on a very granular level very easily.
  • I get much simpler transactional integrity when inserting files than I would with a file system.

The best argument against database storage are around size - the database might become unmanageable. Much more difficult to do database backups & maintenance; the database is one huge file, whereas if the files are external then you can have some over here and some over there.

If you're using Microsoft, perhaps the best compromise is the FileStream type. It stores the filepath in the database and the files in a folder, but the folder is essentially controlled by SQL. You 'insert' files to the database and they're saved in the folder, and when you 'select' from the database the server will retrieve them from the folder and serve through through the database. In this way you get the best of both worlds - transactions and control, plus the size efficiency of keeping the database small.

Related Topic