With the FILESTREAM attribute now available to SQL Server T-SQL developers as of SQL Server 2008, you are faced with questions that I hear from Oracle PL/SQL developers as well: when to store a large binary object (BLOB) directly in the DB and when to point to the file on the server’s file system. With FILESTREAM in SQL Server 2008, you can use a varbinary (max) column and SQL Server will use the file system to store the data. There are a few caveats and limitations to be aware of. But this does make access of BLOBs very fast from SQL Server. I found the TechNet entry on FILESTREAM to be most useful for those points.
This concept is similar to Oracle’s BFILE and a rule of thumb is that files over 1 MB should utilize FILESTREAM or a file pointer instead of storing the object directly in SQL Server. I liked Don Schlichting’s write-up on this Database Journal, so click on that link and check it out.