Discussion in 'T-SQL Performance Tuning for Developers' started by hirnani77, Jan 1, 2003.
Any suggestion about how to store the images into database using image data typa
Refer to this link http://www.sqlteam.com/item.asp?ItemID=986] which explaines the scenario.
But you need to asses the database size if need more images in the database, from this aspect its suggested to store image files (.jpg or .gif) on a share directory and link from SQL server to store the URL or path for that image file.
Although storing images in SQL Server is possible, it is often not practical. For more information, besides what satya has provided, check the Books Online. But as satya has said, most people store images on disk, and only refer to the image (path or url) in the database, because it is much faster and easier to work with.
Brad M. McGehee
The biggest advantage for you storing the path instead of the picture itself in the database, is that it will be much easier to obtain the image. I guess I could understand storing the image in the database IF IT WAS EASY to get out, but that is NOT the case.
"How do you expect to beat me when I am forever?"
And the other concern (for DBA) is database space, size does matter <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Satya SKJ<br />
I have to create a system using sql and retrieve image file from database. Hopefully someone can give some good suggestion. I know it is not practical but have to do it because of my thesis.
What development language are you using ?
Most languages will have sample code on the net somewhere for storing images.
You can get few good code examples fromhttp://www.swynk.com andhttp://www.planetsourcecode.com for this kind of thesis.
Yes!! Storing images directly in the SQL Server is not practical. Have faced enough problems with storing images in DB. Its better to store the image as files in the server and store the path in the DB.
Maintaining these images is one of the major issues.
Anyway its for your thesis which is one time dealing you can go ahead with specified links.
Yep, have to agree with everyone else. I inherited a database where images are stored in the database and it is major problems. We use .asp pages to update the database and it causes headaches frequently, not to mention the massive size of the database. geez..
MS, MCDBA, OCA, CIW
I am curious as to why the image datatype in SQLServer is considered to be a potential performance issue. I am currently an Oracle DBA and we are considering moving an application to SQLServer. In the existing app we store over 200,000 images (avg size 24k) in our database. Approximately 200 new images are inserted each day and then they are used mainly for read-only transactions. We prefer this method because it greatly simplifies our backup and recovery procedures and the DB security model encompases the images as well. If we implemented the method of simply storing the url or path in the DB and the associated file in the file system, then we would have to develop procedures to insure the file system is in sync with the DB in the event of a recovery and also have to develop a similar security scenario in our file system. In addition would it not cause performance problems storing several thousand files in one directory, or is recomended to use numerous directories? This is an important issue in our decision and any input or feedback would be appreciated. Thank You.
In SQL Server IMAGE datatype variable-length binary data from 0 through 231-1 (2,147,483,647) bytes which is multiplied accordingly when any new record inserted.
Most of the information is listed above and its always suggested to manage images from file path rather storing directly in the database. It causes more issues when the DBCC and any maintenance checks running against that table particularly.
pdensmore, the point you make storing the image in the database because of the convenience is a valid point. It does make somethings easier, and it your case, it might be the correct decision. But if you do this, you will have to live with the problems associated with it. I know its a pain, but I suggest you perform some tests with storing data in SQL Server and see if you can live with this option.
Brad M. McGehee
Does anyone have codesamples on storing path to an image in sql, and displaying the images on a web site.
"Inserting an Image into a Column"
pdensmore, one of the reasons why it is discouraged to store image files in SQL Server is because the data pages containing image data are stored saperatly than data pages containing rest of the data for the table unless specified otherwise. Hence there is a performance hit. I am not sure if this is the case with Oracle or not but any insight would be appreciated.
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Ok, just for the records [<img src='/community/emoticons/emotion-1.gif' alt='' />] Here is the most comprehensive link on this topic, IMHO <br /<a target="_blank" href=http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp>http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp</a><br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
Separate names with a comma.