FileStream and FileTable are used to store unstructured data in the database. You can store BLOBs such as Word documents, PDFs and images.
FileStream was introduced in SQL Server 2008 as a way to store BLOBS in a table on a relational database. FilesStream is not a datatype and is enabled as an attribute on a VARBINARY(MAX) column. FileStream allows us to manage our unstructured data and stores them as files on the file system.
The FileStream column is not cached in the SQL Server buffer cache and is cached in the Windows NT system cache. This means that the SQL Server buffer pool is not used by FileStream data, therefore the memory available for query processing is not reduced.
FileStream allows transaction consistency between the other data stored in the database. You are able to backup and restore the data stored in the file groups which contain FileStream data. The full-text search functionality on FileStream columns works the same way as it would on a VARBINARY(MAX) column.
When storing a file/image on a FileStream enabled column, only a pointer to the file is stored in the table and the actual file is stored in the Windows NTFS.
FileTable was introduced in SQL Server 2012 and builds on top of existing FileStream capabilities. FileTable allow you to store directory hierarchies in a SQL Server database, and addresses the requirements for non-transactional access and Windows application compatibility for file-based data. FileTable is a specialized table with a fixed schema. FileTable can also be queried and updated through normal T-SQL commands.
For non-transactional file access for Windows based API applications the file and directory data in a FileTable is exposed through a Windows shared location. This looks like a normal network share location with its files and directories to the Windows application.
Compare FileStream and FileTable
|Feature||FileStream Solution||FileTable Solution|
|Single story for management tasks||Yes||Yes|
|Single set of services: search, reporting, querying, etc||Yes||Yes|
|Integrated security model||Yes||Yes|
|In-place updates of FILESTREAM data||No||Yes|
|File and directory hierarchy maintained in the database||No||Yes|
|Windows application compatibility||No||Yes|
|Relational access to file attributes||No||Yes|
Table reference: https://msdn.microsoft.com/en-US/library/hh403405.aspx
Pros for using FileTable:
- Windows API compatibility for file data stored within a SQL Server database. This includes the following:
- Non-transactional streaming access and in-place updates to FILESTREAM data.
- A hierarchical namespace of directories and files.
- Storage of file attributes, such as created date and modified date.
- Support for Windows file and directory management APIs.
- Integrated storage and data management capabilities such as backup.
- Integrated services such as full-text search and semantic search over data and metadata.
- Ease of administration and policy management over the unstructured data.
- Ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table
- Allows SQL to take advantage of the NTFS I/O streaming capabilities
- Buffer pool is not used therefore the memory available for query processing is not reduced
Cons for using FileTable:
FileTables do not support memory-mapped files such as NotePad and Paint. You are not able to use these applications on the same server as SQL Server to open the files stored in a FileTable, however, you can use these applications from a remote computer to open files that are stored in a FileTable.
In order to use FileStream or FileTable you will need to enable FileStream on the instance of SQL Server, then enable it on the database. I shall go into more details on setting up FileStream and FileTable in my next blog post.