How to Store and Retrieve Images Using SQL Server (Server Management Studio)

Question

I am having difficulties when trying to insert files into a SQL Server database. I'll try to break this down as best as I can:

1. What data type should I be using to store image files (jpeg/png/gif/etc)? Right now my table is using the image data type, but I am curious if varbinary would be a better option.

2. How would I go about inserting the image into the database? Does Microsoft SQL Server Management Studio have any built in functions that allow insertions of files into tables? If so, how is that done? Also, how could this be done through the use of an HTML form with PHP handling the input data and placing it into the table?

3. How would I fetch the image from the table and display it on the page? I understand how to SELECT the cell's contents, but how would I go about translating that into a picture. Would I have to have a header(Content type: image/jpeg)?

I have no problem doing any of these things with MySQL, but the SQL Server environment is still new to me, and I am working on a project for my job that requires the use of stored procedures to grab various data.

Any and all help is appreciated.

Answer 1

There is bulk insert util acceptable from command line bcp.exe. Read this for more information how to insert binary data into MS SQL through t-sql.

Answer 2

The image type is deprecated, so don't use that.

The short answer is to use the FILESTREAM type if this is SQL 2008, or varbinary(max) if it's 2005 or earlier.

The better answer is to use the FILESTREAM type if this is SQL 2008, and not store images in the database at all otherwise. Databases aren't built for this type of thing and throwing BLOBs in there can hurt performance significantly. FILESTREAM sidesteps the issue by storing the actual data on the file system; everything else is a sub-standard solution.

As for how to read and write to a varbinary column if you choose to take this non-ideal approach, it's going to be represented as a stream in PHP. That's what you get out of a query as the column value, and I believe that's what you have to put in as a parameter.