Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Storing images in database Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 5:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:28 AM
Points: 268, Visits: 680
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.
Post #1356673
Posted Monday, September 10, 2012 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, 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.
Post #1356712
Posted Monday, September 10, 2012 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,228, Visits: 12,705
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 Moden's 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)
Post #1356800
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse