FileStream vs FileTable

Question

I want to store images in a sql database. The size of the image is between 50kb to 1mb. I was reading about a FileStream and a FileTable but I don't know which to choose. Each row will have 2 images and some other fields.

The images will never be updated/deleted and about 3000 rows will be inserted a day.

Which is recommend in this situation?

Answer 1

Originally it was always a bad idea to store files (= binary data) in a database. The usual workaround is to store the filepath in the database and ensure that a file actually exists at that path. It wás possible to store files in the database though, with the varbinary(MAX) data type.

sqlfilestream was introduced in sql-server-2008 and handles the varbinary column by not storing the data in the database files (only a pointer), but in a different file on the filesystem, dramatically improving the performance.

filetable was introduced with sql-server-2012 and is an enhancement over filestream, because it provides metadata directly to SQL and it allows access to the files outside of SQL (you can browse to the files).

Advice: Definitely leverage FileStream, and it might not be a bad idea to use FileTable as well.

More reading (short): http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html

Answer 2

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage.

If the following conditions are true, you should consider using FILESTREAM:

• Objects that are being stored are, on average, larger than 1 MB.
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Benefits of the FILETABLE:

• Windows API compatibility for file data stored within a SQL Server database. Windows API compatibility 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.

• Compatibility with other SQL Server features including management tools, services, and relational query capabilities over FILESTREAM and file attribute data.