Images - Store in a database or file system?

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

hi Paul,

PaulN wrote:

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

yes, this often result in "religious wars" bewteen parts.. I'm with the "all in the db" part , with just one caveat (regarding MSDE and SQLExpress), depending on the "limited" database maximum size of 2gb for MSDE and 4gb for SQLExpress.. this obviously could "limit" the quantity and size of the lobs stored in the database, thus penalizing an architecture of a project..

well.. 4gb are not that "limited" if you store little jpges, but could eventually become a real limit if you have to store a "tipycal" ripped movie of about 700mb..

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

actually that's not that bad... it's real a "limited" quantity you should not have problem with..

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

this is true.. standard disk space is absolutely cheap. but you incour in other kind of problems.. see later..

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

thi is the problem with "external" resources... I mean that it's a traditional scenario for this kind of stuff, but this includes an important access tradeoff.. if you have "all your things" within the database, you only need to query it for returning the desired output (including the lob's data).. all is incapsulated within the database... you do not have "particular" security problems as your database permissions schema already includes lobs.. on the contrary, if your "table" just stores the "path" to the actual lob jpeg file, that file should be available among the whole lan (if your scenario involves remote connections), meaning it must reside on a "share" where all potential (remote) users can access it, with read-only and/or read/write permissions on it (depending on the nature) in order to allow both access (read) and modifications (write)...

more, all the automated tasks as you already pointed out are out of scope, you have to load pictures from a stream loaded with the lob's data from the (remote) folder... this last part is not that bad (streaming data), but all the remaining part is (accessing physical NTFS permissions, share, and disk subsystem), IMVHO...

I do prefer to streams lob's data out of a (say) datareader object anyway from the database not directly relying on wizard's bindings as usually data have to be "purged/checked" before presentation (but this is not mandatory, it depends on your design and the actual prj), but all remains "incapsulated" in the db... just a few stored procedures to read/write data in and out and the job is done...

regards|||

I agree with Andrea.

For small photos, store them in the database. You won't 'lose' photos quite as easily as if they are just 'floating' around the file system somewhere. And backups are much easier.

However, if I were dealing with something like a Contract Photo Agency, with hundreds of thousands of photos, in high resolution (meaning multi-MB photo/file sizes), I would investigate something like 'FileNET', or other of the content management 'systems'.

|||Another good ressource is: To blob or not to blob: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql