Of BLOBs and FILESTREAM

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s