ADVICE ON BEST PRACTICE: Storing Binary data in SQL

[specialist]

Greetings-

I am in the process of moving a DB from Access to SQL for an application and one column holds an OLE OBJECT in the access db, which converts to Image datatype in sql. I am not sure if I would like to hold the images, zips, docs, etc inside the db as it seems to bloat the db immensely.

My question to the wonderful community of tek-tippers is this: Is there a best practice for this sort of situation? Is it better to hold the binaries in the DB or would if be better to have a path to the file in the column instead?

I have looked far and wide and there seems to be a 50/50 split on the matter. In my particular situation, the conversion from .mdb to sql is to improve performance and scalibility...aren't they all

Any advice as to a "best practice" in this matter would be welcome. Anyone who has already been in this situation please let me know.

Muchas gracias!

-Mike

 

[SQLSister]

Personally I find it much easier to store the path of the file. Especially if the file will be updated. Much easier to store the word document and let the owner change it in Word than to have to havea process whereby the document that is changed also then has to be added to the database or replce another document inthe database. Never hasd any troule with speed in web pages accessing dat this way, but theother could I suppose be faster. I don't know for sure as I never use that method. If youwant the most efficent method as far as displaying the images, I suggest you try iut both ways and see which works best for you.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

Thumbnails in db, full-images no.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[specialist]

SQLSister and Donutman-

First off, thank you for the insight as to how to tackle this issue. I appreciate the advice.

Just to add one important bit of info: there are no images files being stored (no jpg, gif, etc). These files are mostly .doc, .zip, and a few .pdf files. No images at all. The reason I have "images" there is that was the datatype selected when I imported the data. I just tried importing the tables with the files (as image datatype) and the db balooned to 260MB froma 65MB access DB!!!




mike