FileStream vs FileTable

sql-server filetable sqlfilestream
Eelco picture Eelco · Sep 1, 2015 · Viewed 8.9k times · Source

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

DdW picture DdW · Jan 19, 2017

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.

was introduced in 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.

was introduced with 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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More