FileStream vs. FileTable

Today I want to talk a little bit about the difference between the Sql Server FileStream introduced in 2008, and the SQL Server FileTable introduced in SQL 2012. Both features share the common capability of integrating file data with a SQL Server database which has been enabled to use FileStreams. A file stream is a pointer to a byte array stored on a file system. SQL Server can be configured to store a FileStream in a native fashion, much like an operating system.

Using the FileStream capability from SQL Server 2008, you create a filestream column in a table having a column of the data type VARBINARY(MAX), and the FileStream attribute set on the column. TSQL commands may now be used to modify the contents of the file stream. The data is integrated with SQL Server transactions, backups, and restore capabilities. When using a filestream data type, you can determine if you wish to store the data inside the SQL Server database, or on an NTFS hosted volume. All access to the contents are made through SQL Server.

FileTables extend the 2008 FileStream capability. Instead of you defining the table, the filetable schema is already defined. In this case the access is different, and quite flexible. You can define if the data participates in transactions, and to what extent. Additionally, you can enable access to the contents of a file system through the operating system, bypassing TSQL. You can even add contents to the file type by pasting files in the filetable host directory. The new files are now visible to SQL Server.

This is a good area for you to read up on integration of Binary Large Objects (BLOB) data with SQL Server. The two implementations may both be used, even in the same database,, and have their advantages. Key to the difference is the ACID nature of the different storage.

Check out:

https://msdn.microsoft.com/en-us/library/gg471497.aspx for file streams

https://msdn.microsoft.com/en-us/library/ff929144.aspx for file tables