SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storing images in database


Storing images in database

Author
Message
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 798
Hi,
Just for the sake of information i was learning how to store data into database. I came across 2 main options regarding storing of image file. First was using varchar(max) which they(author) said that is know as BLOB storage. Second option which they were saying is of storing of image url.

Since i am a newbie i don't know the technical difficulties or technical differences between the 2 method but i would like to learn. Could you provide some explanation or some link from where i would get the above information.
t.brown 89142
t.brown 89142
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 140
They probably meant VARBINARY(MAX) - which gives you 2GB of binary data - enough for [almost] any image. This is the BLOB method.

its as simple as this. If your application can convert the image to an array of bytes - e.g. if your using .NET WebClient.DownloadData(Url), then you can pass that data to SQL to store in a varbinary(max) field.

Otherwise, you can just store the Url in a VARCHAR(1024) field - lenght determined by how long your Urls are likely to be.

Technically its more complex to then serve the image back from a BLOB, as you need to create a Response with headers, and write the binary data to it. If you just serve a Url, the web browser downloads it and displays it correctly.

There will be increased traffic between the SQL Server and Web Server using the BLOB method.

Either method is acceptable. Its really a matter of examining how many images you are dealing with and whether you want them on the SQL SErver or on the WebServer.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63588 Visits: 17966
A third option which you did not mention is to use FILESTREAM. http://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx

This is a valid option in many situations.

With either of the previous methods there are some pitfalls. If you store it in the DB the DB becomes VERY large and you should make your files in a separate table. If you store the path then you have to validate the file existence when you need to access it. You also have to allow more permissions to network resources to your application/database users. There is no "perfect" way. You need to do some research and determine which is the best methodology for your situation.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search