image datatype vs URL - pros & cons
-
My boss wants to start storing images in the db. All our apps store images on disk with a varchar column in the table containing the URL. Sql-Server-performance.com advises "The image data type should generally be avoided because of its poor performance." Does anyone have real life proof/experience that this is true? What are the pros and cons of each method?
-
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp explains things a bit.
I for myself do store BLOBs in a db. That is a separate db that serve no other purpose than this. BLOBs are only stored, not updated anymore. That DB is some 3 GB in size with BLOBs (mainly PDFs) are in the range from 26kb up to 10MB.
There is no heavy traffic on the tables.
All in all for my situation the advantages to have one single backp mechanism and that I don't need to care about data inconsistencies outweight the *might be* disadvantages.
But I do also think there are other constellations. You'll find that this is a more than a simple question, sometimes it turns out to be rather philosophical
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Assuming I want to write an ASP app that can display BLOB's stored in a database (i.e. Employee pictures), is it absolutely necessary to retrieve the BLOB to a file system object and then ref the IMG SRC= to the FSO? Or is there a way to ref the IMG SRC= directly to the SQL BLOB?
From my perspective this would be the deciding factor on whether or not I store images as BLOBs. If I have to read them from the database and create a file, then generate the HTML to display the file vs. getting the name of an existing file and just displaying it I'd rather do the latter. I can always write auditing processes that ensure synchronization between the database and file system object collection.
-
It is far simpler:
<!-- #include file="top.asp" -->
<%
Dim oRecSet
Dim oConn
Dim SQL
Dim szType
Dim lpszMsgID
Response.Clear
Response.Buffer = true
Response.Expires = 0
Set oConn = ConnectAttachment()
Set oRecSet = Server.CreateObject("ADODB.Recordset")
SQL="SELECT img FROM research WHERE " & Request.ServerVariables("QUERY_STRING")
oRecSet.Open SQL, oConn
If oRecSet.BOF And oRecSet.EOF Then
Response.End
End If
Response.ContentType = "application/pdf"
Response.BinaryWrite oRecSet.Fields("img")
oRecSet.Close
Set oRecSet = Nothing
oConn.Close
Set oConn = Nothing
%>That's all!
Frank
Sorry, I mark the really important stuff bold now
Edited by - Frank Kalis on 12/10/2003 07:10:47 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply