Posted by Erland Sommarskog on 01/31/06 00:17

--CELKO-- (jcelko212@earthlink.net) writes:
>>> How would you store a JPEG image in a database? You would disassemble
it, and store it in some high-level format? <<
>
> 1) Store a file name as a string, so that the application can grab the
> file.

Yes, this is an option. Just as you could store employee names,
number of car doors or what ever on a file, and then store the file
name in the database.

However, it's difficult to get transactional consistency with a file
system. You commit the transaction, but the file could not be stored
on disk. Or the file is stored on disk, but then the transaction was
rolled back for some resaon. Or consider the case that the file system
is rearranged, and all the file pointers become stale.

It is not uncommon to use this approach in SQL Server though, and
the main reason is probably that the image data type has been difficult
to work with. (Which has nothing to do with its binary-ness to do, but
it's largeness.) With the advent of varbinary(MAX) in SQL 2005, there
may be less reason for this.

> 2) If this is a serious problem, then use an image base tool. IBM has
> one that lets you make a drawing as the query and it will pull out the
> ones that are closest; there are several products that do nothign but
> face and fingerprint matching; CAD systems that handle 3D objects; etc.
>
> SQL is not the answer to everything.

But it's surprising to hear that it's not the tool to store data.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]