FILESTREAM

FILESTREAM is a Microsoft SQL Server feature that enables storing unstructured data, such as images, documents, and videos, in the Windows file system while maintaining transactional consistency with the relational data stored in SQL Server. It works by pairing a varbinary(max) column declared with the FILESTREAM attribute with a dedicated FILESTREAM filegroup. Data stored in that column is written as files on a designated NTFS directory, with a pointer kept in the database to maintain transactional integrity.

In a FILESTREAM-enabled database, a FILESTREAM filegroup points to a directory on disk. When a row contains data in the FILESTREAM column, the actual binary data is stored as files in that directory, while a record in the database holds a link to those files. Access to the data can be performed through standard Transact-SQL operations on the FILESTREAM column, and, if FileTable is used, through regular Windows file system interfaces as well.

Setup involves enabling FILESTREAM at the SQL Server instance level, enabling FILESTREAM for the database, creating a FILESTREAM filegroup and a FILESTREAM data file, and creating a table with a varbinary(max) FILESTREAM column (typically with ROWGUIDCOL for row identification). Backups of the database include the FILESTREAM data, ensuring coordinated restore of both relational and unstructured data.

Usage considerations include Windows/NTFS requirements, and the fact that FILESTREAM data is not supported in memory-optimized tables. It is generally used for large binary objects where streaming I/O can improve performance and reduce log contention. Related features include FileTable, which exposes FILESTREAM data through the Windows file system namespace for applications that rely on standard file APIs.