Using BLOBs in SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jawallaby - 25 Aug 2005 14:44 GMT

Can anyone tell me what the pros/cons are of using blobs in sql?

We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.

I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.

Any help will be appreciated.

Reply to this Message

someone@work.com - 26 Aug 2005 20:44 GMT

I am also curious about this...
I've been looking at methods of storing PDF files
and I'd have the exact same questions....
Any help is appreciated.

TIA

Carter Lekatz
*xnoxspamx*c.lekatz@*xnoxspamx*halconcorp.com
remove *xnoxspamx* to reply

> Can anyone tell me what the pros/cons are of using blobs in sql?
>
[quoted text clipped - 7 lines]
>
> Any help will be appreciated.

Reply to this Message

Adam Machanic - 26 Aug 2005 21:11 GMT

Here are some views:

http://www.aspfaq.com/show.asp?id=2149

Signature

Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

> I am also curious about this...
> I've been looking at methods of storing PDF files
[quoted text clipped - 19 lines]
> >
> > Any help will be appreciated.

Reply to this Message

John Kane - 30 Aug 2005 04:26 GMT

I've not reviewed all of the referenced KB articles in the aspfaq.com link,
a brief review tells me that not one makes reference to Full-text Search of
files stored in SQL Server 2000 tables! Adam, perhaps you could get them to
add a few? <G>

I just blogged how to upload (import) PDF files into a SQL Server 2000
table's IMAGE column (with "file extension" column) and Full-Text Index
(FTI) and Full-Text Search (FTS) the contents of these files after
downloading & installing the Adobe IFilter:

Full Text Search Adobe PDF files stored in SQL Server...
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!537.entry

Enjoy!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Here are some views:
>
[quoted text clipped - 24 lines]
>> >
>> > Any help will be appreciated.

Reply to this Message

Adam Machanic - 30 Aug 2005 14:47 GMT

I will mention it to the author (Aaron Bertrand)!

Signature

Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

> I've not reviewed all of the referenced KB articles in the aspfaq.com link,
> a brief review tells me that not one makes reference to Full-text Search of
[quoted text clipped - 7 lines]
>
> Full Text Search Adobe PDF files stored in SQL Server...

http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!537.entry

> Enjoy!
> John
[quoted text clipped - 26 lines]
> >> >
> >> > Any help will be appreciated.

Reply to this Message

Jose G. de Jesus Jr MCP, MCDBA - 30 Aug 2005 11:53 GMT

I suggest you store it as a blob in the database
rather using pointer to files.

the reason is security.
as long as no one enters the db then no one gets the files
the second reason is that i have my files organized in the db
providing fields and attributes that can properly describe the
files such as authors, datecreated and version.

if you are designing as system that uses versioning and check-in checkout
feature
i suggest you use this approach.

you must however store the blob field in a separate file group to avoid
fragmentation

Signature

Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

> Can anyone tell me what the pros/cons are of using blobs in sql?
>
[quoted text clipped - 7 lines]
>
> Any help will be appreciated.

Reply to this Message

Jose G. de Jesus Jr MCP, MCDBA - 30 Aug 2005 11:58 GMT

i've designed similar appilcation before.
i use msoffice 2003 to do the OCR store the
the granular text to a text field
and then i store the image into the blob

then i made use of the textfield for my
full text search

when i want to retrieve the document
i retrieve the scanned image

its neat if everything is in sql
i'm in full control of versioning

Signature

Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

> Can anyone tell me what the pros/cons are of using blobs in sql?
>
[quoted text clipped - 7 lines]
>
> Any help will be appreciated.

Reply to this Message