| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-16 : 08:20:56
|
| Tushar writes "Hii I would like to know how to store and retrive Pictures from sql server databse?" |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-03-16 : 08:29:13
|
| Lookup 'image' in books on-line. The section 'Managing ntext, text and image data' is a good starting point.Raymond |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-16 : 09:03:41
|
| The best answer to this is probably "Don't store images in SQL Server." If you are being forced to, that's fine. It's much better to just hold a pointer to the file though in SQL Server. Have a table with a field that points to the directory/file name the image is stored in. This will save you on backup times for SQL Server. You can just run a full/diff backup schedule against the images. It's also friendlier on your database server as SQL Server doesn't handle images particularly well.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-16 : 09:17:10
|
Personally I think there is no absolute black or white to this question. Both approaches are valid and have their advantages and disadvantages. However, one first needs to build up a solid knowledge to decide which way to go. --Frankhttp://www.insidesql.de |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-16 : 09:38:05
|
| I have one db that is solely for BLOBs. Mostly pdfs ranging from 26KB up to 10MB. Right now the db is about 2 GB in size. No problem this far. Once the BLOBs are stored, they aren't updated anymore. There is no frequent access to that data....to be honest, nobody cares about the data. But I bet, when I stop storing or even delete the data, next minute I get a call from management asking exactly for that data. --Frankhttp://www.insidesql.de |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-16 : 09:46:31
|
quote:
But I bet, when I stop storing or even delete the data, next minute I get a call from management asking exactly for that data.
LOL...sounds like right out of a Dilbert strip! OS |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-16 : 10:03:03
|
| Yeah, sometimes it is so. Sad, but true.My favorite strip is where the boss is saying to Dilbert:Boss: "I have fired Ted and sourced out his important tasks. You are going to do his unimportant tasks."Dilbert: "Why are we doing unimportant tasks?"Boss: "Because we can." Can't send the strip as I read it in a newspaper.--Frankhttp://www.insidesql.de |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-16 : 11:39:42
|
My all-time favorite comic is Calvin & Hobbes, it's like no other! The depth of words that come out of the little kid's mouth and his (imaginary?) pet tiger, shows the genius of the artist. It's a pity he stopped drawing C&H as a protest against unethical publishers  . My favorite ones:  OS |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-03-16 : 14:16:22
|
| I have found what I consider to be an effective method for storing Images within a SQL DB. I create a separate table to store the images with only 2 columns one is an Autonumber Int Key field. The second is the Image. I then create an SP that will only call one image record based on the key field. When I create an application I place the Image Viewer in a sub form or report. And pass the “Key record” to the SP from the base form or report.This overcomes the speed problems when calling images from SQL. The advantage for me is that this is backed up as part of normal SQL backup and since I run a backup server with Replication the whole thing is hands off. JimUsers <> Logic |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-03-16 : 15:46:46
|
| I have a VB app that will allow users to select an icon and then store that icon in an image field. I found that with small files like that, the SQL is a good solution. The tricky part that I ran into is displaying those images. You have to store the binary data into a temp file and then display that temp file.So, being a know-it-all-genius I thought, well I will just create and save the image on the users machine so they don't have to pull it from the database each time! Then Windows slaps me with permissions that greatly complicated the issue! In a nutshell, there are a variety of ways to tackle this. The size of the image is to me, the most important piece. If you are talking about 3kb icon files then sure use the image field, but if we are talking about storing images that are gifs or jpgs, then I would only use the SQL tables to store network paths to the images.Can you give us more details?Thanks!Aj |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-16 : 23:31:19
|
quote:
The size of the image is to me, the most important piece. If you are talking about 3kb icon files then sure use the image field, but if we are talking about storing images that are gifs or jpgs, then I would only use the SQL tables to store network paths to the images.
Actually, small images are the WORST ones to store in the DB. Text and image data are stored on regular 8K pages in SQL Server. Anything less than 8K will still use a full page, with the rest wasted. If you have hundreds of small images you can easily waste 50% or more of the disk space. Granted this may not end up being a whole lot, but it makes backup files bigger and longer to run/restore. Larger images will still be stored in 8K fragments and may waste proportionally less space, but it still adds up. SQL Server also allocates 8 pages into a single extent, and depending on how the data was originally inserted you can end up with mixed extents that could potentially fragment the image file over a wider portion of the disk, increasing the I/O needed to retrieve it. Not to mention that since the database is stored on a filesystem anyway, you'll have the additional overhead of the database processing required to get the image. Storing it as a file would be practically free in comparison.You also cannot back up or restore individual images if they are stored as rows in a database, at least not without A LOT of work, far more than simply copying a file would entail. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-17 : 02:38:50
|
I don't really like this kinda 'religious' discussions. It the same with the use of NULLs. We won't solve ultimatively such questions here, so all that can be done is to give arguments on pros and cons and the decision is up to the original questioner.BTW, mohdowais, if you have children you appreciate Calvin & Hobbes even more. Especially the part of the parents suddenly becomes very evident  --Frankhttp://www.insidesql.de |
|
|
|
|
How to read and store images in the database