Key points on using Filestream attribute

According to this study from Microsoft Research, SQL Server handles BLOBs smaller than 256KB more efficiently than a file system, while NTFS is more efficient for BLOBS larger than 1MB. SQL Server 2008 introduced the Filestream storage attribute for varbinary(max) data type to store data in files.

Here are the steps to begin using this feature:

Enable Filestream storage at an operating system level using SQL Server Configuration Manager (system administrator). Then enable it on a SQL Server instance level using sp_configure (database administrator). See specific steps here or here.

Define a database filegroup that ties an NTFS file system location to a SQL Server database. It must be a local file system location.

Add a varbinary(max) column to a table definition with the FILESTREAM property. Add a uniqueidentifier column with the ROWGUIDCOL property (required).

Read and write the data through the stream-based APIs to get the performance advantage attributed to the Filestream property. In .NET 3.5 SP1 such support is provided by the SqlFileStream class.

Example of column definitions:

  Photo VARBINARY(max) FILESTREAM NULL,
  RowId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID()

Reference:
Bob Beauchemin, Programming with FileStreams in SQL Server 2008, MSDN Magazine