Storing images in database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Storing images in database

Storing images in database

Rate Topic

Display Mode

Topic Options

Author

Message

Shadab Shah

Posted Monday, September 10, 2012 5:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:07 PM
Points: 162, Visits: 336

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

t.brown 89142

Posted Monday, September 10, 2012 7:14 AM

SSC-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

Sean Lange

Posted Monday, September 10, 2012 8:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 8,585, Visits: 8,227

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

Post #1356800

« Prev Topic | Next Topic »

Permissions