September 10, 2012 at 5:43 am
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.
September 10, 2012 at 7:14 am
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.
September 10, 2012 at 8:56 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply