Chapter 26. SQL FILESTREAM: to BLOB or not to BLOB
Coming from a development background, I understand streaming and I/O operations; the two concepts fall close together in my mind. So, when I think about SQL Server, I think about data in rows and columns and managed by an engine—files don’t enter my mind.
FILESTREAM is a new SQL Server data type for SQL 2008 and later. More precisely, it’s an attribute on an existing varbinary(max) data type in SQL 2008 and later. There are requirements to use it, as you’ll see in this chapter. FILESTREAM gives the SQL Server engine the ability to store a reference to a file in a SQL Server column and have the actual file stored in the filesystem instead of inside the column itself. But the question is, as this chapter’s title asks, do you store the file in a BLOB, inside the column (to BLOB), or is it better to store it in the filesystem (not to BLOB)?
In this chapter I’ll show you the requirements and configuration steps to get this attribute to correctly apply to a column in a table so the file is stored in the filesystem instead of in the data pages. I’ll also discuss when you might want to use FILESTREAM and when you might not.
In the early days of content management systems (CMSs), there were various ways to handle files in databases. Here are the main three methods often used for storing files:
