Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Appendix E. FileStream Objects and Syntax

The filestream data type, introduced in SQL Server 2008, provides a mechanism for storing and synchronizing content in the server file system. This is ideal for managing large volumes of unstructured data, typically stored in documents and other application-specific file types. Data for filestream type columns is inserted into a database table but actually stored in separate files outside the database — in the NTFS file system, instead of the SQL Server database files. All T-SQL query actions (i.e., INSERT, UPDATE, DELETE, and SELECT) interact with the externally stored data that is managed by the SQL Server database engine.

This feature is disabled by default and must be enabled using the sp_filestream_configure system stored procedure.

A database is filestream-enabled by defining a filegroup based on a file system folder, called a file container. The database file for this filegroup stores metadata, settings, and header information used to manage the external data.

A table is filestream-enabled by defining a column with the FILESTREAM keyword when the table is created. Data may be written to the filestream type column and subsequently stored in the NTFS file system, using a standard T-SQL INSERT statement for the filestream-enabled table, or by using managed or unmanaged program code.

Because the original content for filestream data will often be contained in source document files, inserting or modifying this data will need to be performed ...