Editorials

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

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Greg Hamilton

    Ben, the link for file tables isn’t valid (the x at the end of aspx isn’t part of the href). Also, that annoying bounce in the blog page while reading is still there.

    • Ben

      I’ll pass that on to our staff about the bounce.

      The href is my bad. Thanks for letting folks know. Once I post something, I can’t fix it. I can’t preview the contents before posting. I’ll see of sswug can help me there too 🙂

  • David Eaton

    Great subject here. I hope it helps folks decide how to use the tools correctly!

    Personally, I am more in favor of Filestreaming over File Tables because they are only accessed withing SQL Server. The improved security, in that only SQL Server can access the filestreamed blobs. File Tables are accessible from outside SQL Server and that makes them a security issue. And I know that the file tables can be locked down.But keeping things simple sometimes is a good thing.

  • Shawn Clabough

    You’re missing the x in .aspx on your file table link.

  • Pingback: FileStream vs. FileTable | data-konzept()