asked
SQL query code
which 'SQL query' code is needed to insert an image into a Image column of SQL server 2008R2.
Given that you have a table called BlobData with a column called BlobDesc and another column called BlobColumn and an image on your hard drive called BlobImage.jpg in C:\Temp then this code would do what you are asking. INSERT INTO BlobData ( BlobDesc , BlobColumn ) SELECT 'First test file' as BlobDesc , BulkColumn FROM OPENROWSET(Bulk 'C:\temp\BlobImage.jpg', SINGLE_BLOB) AS BlobColumn Are you sure you want to put the whole image in your database though? Why not just insert the file location in your table and keep the image on the file server?
Storing binary data directly in SQL Server isn't that bad from a performance perspective and it gives a lot of flexibility as data grows, or you need to move to other database servers etc. I've implemented such solution for a picture database system, with some 100 gigabytes of pictures (both the high resolution picture and other resolutions). As data grows, we added new disks and distributed the database over new files on the new disks. The system didn't have to bother about the pictcures being spread over different drive letters. It also took away the need for giving users direct access to the file system on the server where pictures were stored. There was of course a slight performance penalty, but it wasn't ever noticable. We used the lower resolution versions of pictures for an online product catalogue, and since all, or next to all, lower resolution pictures fitted in SQL Server's cached memory, the response time was actually stunning.
Think how fast your backup and restore would have been if you didnt have any of those images in the database ;) Item level restore from the file system backup application is a lot easier than row level from a database backup. I can see your point though. At the end of the day either is the right choice if you go into it with an awareness of the advantages and disadvantages of each method.
The worst-performing database (in terms of backup & recovery times) that I deal with at the moment is one that contains 90GB of PDFs...
I take your point on restore. Backups though would be faster with the in-database solution. Full-backup of our 50.000+ pictures would have been slower than a full DB-backup :) Totally agree though: next to any architecture is the right one, for some scenario. The problem i matching...
11 People are following this question.
