Storing images in SQL vs. storing path to image file

I'm writing a document management application whereby users will be able to scan documents into the application. Then from within the applicaiton the users will be able to access the scanned documents.

So the debate is whether I should store the image directly in SQL Server (7.0) or simply store the path to the image and put the image on a network folder.

I have heard people recommend storing only the path and not the image itself but I don't know the reasons.

Obviously the database would be quite large but can't SQL server handle it? Backups would have to be performed either way so... I suppose if the database was corrupted then all images would be lost as opposed to single image files being corrupted on the harddrive.

I guess I'd like to hear what others have done in the past, their opinions, etc.

Thanks in advance.

Depending on the number of images you can really blow out a db size and slow the thing down no end.


Would suggest storing the path, but if you must store the images then Beaker has a tutorial up for it in the db forum, first thread

Cheers,

Hope that helps...

bump...

would anyone care to explain in more detail just how to store a reference to the file In SQL where it points to the image in a folder ??
would help me alot :):)

Not necessarily a very detaild explanation but... say you have a folder on the network where you store your images:

\\SERVER\Images\

Simply store the full path and filename of the image in your database:

VB Code:

Dim rsImages as RecordSet
Dim objConn as Connection
 
'open connection object based on your provider
 
rsImages.Open "Images_T", objConn, adOpenKeyset, adLockOptimistic
 
With rsImages
     .AddNew
     .Fields("Name") = "This is my test image"
     .Fields("File") = "\\SERVER\Images\test.jpg"
     .Update
     .Close
End With
 
Set rsImages = Nothing
objConn.Close
Set objConn = Nothing

Then whenever you need to do anything with the image just load it from disk based on it's filepath.

VB Code:

' assuming an Image control on a form
 
Dim rsImages as RecordSet
Dim objConn as Connection
 
' open connection based on your provider
 
rsImages.Open "SELECT * FROM Images_T", objConn, adOpenForwardOnly, adLockOptimistic
 
If Not rsImages.EOF Then
     While Not rsImages.EOF
          With rsImages
               Image1.Picture = LoadPicture (.Fields("File"))
               MsgBox "Press Ok to display next image...", vbOkOnly, "Continue?"
               .MoveNext
          End With
     Wend
     MsgBox "No more images...", vbOkOnly, "Finished"
Else
     MsgBox "No image records were found...", vbOkOnly, "No Records"
End If
 
rsImages.Close
Set rsImages = Nothing
objConn.Close
Set objConn = Nothing

Let me know if you need more than that.

Thanks I will give it a try :):)
This is the first time I have seen any post with some kinda explaination as to store the image in a folder and ref it in the SQL database.

Thanks again :)

What version of SQL Server are you using? If it 2000, then I don't think it matters too much. They have really made great strides in that area I think. That is unless you need it to be super fast, then you will probably want to only store the paths to the images.

I am no expert at SQL Server by any means, but I think that the performance issues comes from the way it stores the data. SQL Server operates on a page system, if the image flows over a page worth of data, it becomes real burdonsome on the SQL Server engine. Plus, when it does page splits and such on a indexed table, it will take longer because it has to move that much more data around.... or something like that.

Like I said though, I am by no means an expert on the inner workings of SQL Server, I just remember reading about it somewhere and think that is kind of along the lines of what your looking at. Try looking up paging in the SQL Server Books On Line help files. It may be what your looking for.

Sorry for my ramblings if I make no sense what-so-ever...lol

thanks :)

I have been doing some reading on the subject. I am using SQL 2000 SP2. none of the images are all that large each but its possible each record can contain a number of pictures associated with one client. Agents can take maybe 6 pictures standard size 4X6 I need to store them and have them viewed as thumbnails. But if the user wants to see a larger view they will double click the pic and see it full size.

Again thanks for any and all information on this subject.

I would use a separate table purely for the pictures, with just two fields - one for the picture and one to link to the associated table. (and possibly a third field for numbering within the 'Agent', if needed).

That way the size will only increase the size of the DB by the size of the pictures, rather than adding the room for X pictures for each 'Agent'.

I'm not sure about the thumbnail aspect - you could add an extra field in your picture table for it, or just display the full picture at thumbnail size. Which way you choose depends on the trade off between the database size and network traffic - if the users have slow connections I would recommend storing the thumbnails in hte DB.

If you store the images on SQL server, I guess you use the image data type. There is no need to create seperate tables for this, because SQL server will store fields of type image, text or ntext on seperate pages anyway. If you have multiple images for each record, you are probably better of with a seperate table, but this is because of normalisation rules, and not because it are images.

One thing to consider is that it is probably more easy to load an image from file into a control (eg a picturebox) then it is to load it from SQL server. Reading from or writing to an image in SQL is done in chunks, so you probably have to use byte arrays for this.

thanks that sounds like the best way to go. Having a seperate table for the pic.