Hi all, according MS storing objects into DB is better IF: "the stored objects are on average larger than 1 MB , FileStream is the best option because of the faster read access. For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance". Is this claim still valid? Since I would like to use FileTable (which is based on Filestream of course) to run a project which I don't think has files of this minimum size. Thanks, Alen

Hi @Alen Cappelletti , Here is the official article（09/17/2022） https://learn.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-ver16， which proves that it's still valid. If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

The text you quote is intended for using FILESTREAM (and OpelSqlFilstream + Win32 fileAPI) in comparison with storing data as blobs directly in the database, and it was written when SQL 2008 was released, the feature where FILESTREAM was introduced. FileTable is a different game and it was introduced in SQL 2012. FileTable comes with some other advantages beyond performance, which may make it more attractive for also smaller file sizes.

Hi @Alen Cappelletti , Do you have further questions on this , could we offer more support? If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

FileStream and filetable best minimum size for storing into DB

Accepted answer

PandaPan-MSFT 1,911 Reputation points

2022-09-19T03:12:00.027+00:00

Hi @Alen Cappelletti ,
Here is the official article（09/17/2022） https://learn.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-ver16， which proves that it's still valid.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"
Please sign in to rate this answer.
Alen Cappelletti 992 Reputation points

2022-09-19T07:25:54.303+00:00

OK clear... so if my docs are 300K or 400K as average, and my intention is to use Filetable feature... maybe i s better store it in DB?

PandaPan-MSFT 1,911 Reputation points

2022-09-19T08:25:03.603+00:00

@Alen Cappelletti Accoring to the article. Yes.

Erland Sommarskog 110.4K Reputation points MVP

2022-09-19T08:25:24.587+00:00

You first need to ask: how do you intend to read and store this documents? Through normal T-SQL statements or Win32 operations? If you intend to use T-SQL, there is not that much point in using Filestream or FileTable.

And if your intention is to use FileTable, you have already made your choice: you will store files as files inside the database. (Because that is how Filestream and Filetable works. You are storing files. But they are part of the database.)

Yet an alternative, that many favours, is to store the files entirely outside the database, and only store the file paths in the database. This reduces the size of the database backups, but it also makes it more difficult to uphold consistency between the files and the database. (Which is why I am not that keen on this myself.)

Alen Cappelletti 992 Reputation points

2022-09-19T08:50:44.663+00:00

Erland but...
"if your intention is to use FileTable, you have already made your choice: you will store files as files inside the database..."

But using one of the two features I realized that only the consistency is managed in the DB, not the file itself that will reside into the Filesystem. For this reason the weight will be less (database) compared to storing the image totally in a field inside a table.
Or did I misunderstand the logic, from what you wrote me it seems that the file is in the filesystem and also in the field of the dabatabse for consistency?
The web application will upload files to an FTP folder, which will be processed by a SISS by moving all that to the FileTable share.

ALEN

Erland Sommarskog 110.4K Reputation points MVP

2022-09-19T21:09:09.473+00:00

Lets look at the various options:

1) Store only the file path in the database, and the file in the file system. (Without FileTable, FilesStream etc). This means that you will have to care for consistency yourself. Someone could poke around in the file area and delete files. If server crashes, it can be difficult to get back to a consistent state etc.

On the other hand, the database backups will be a lot smaller and easier to manage. Access to the files will be fast and simple.

2) Store data as regular blobs in the database. No consistency problems, but that database backups will be bigger.

3) Store data as FILESTREAM. Again, no consistency problems. While the files are stored in the file system, they are still logically part of the database. Database backups will be as large as with blobs.

Access to really big file can be faster, if you use OpenSqlFilestream, but this is more advanced programming.

4) Store data in FileTable. This is a special case of FILESTREAM, so files are part of the database. This means that there not the consistency issues that you have in the first case. However, operations on the area are non-transactional, so if the server crashes while a file is being added/updated/deleted in the area, there could be issues with this file. But it is still smaller problem than in the first case.

Since you can use normal copy operations, you don't have to do speciality programming with OpenSqlFilestream.

Erland Sommarskog 110.4K Reputation points MVP

2022-09-19T21:21:27.437+00:00

And just to make it clear: the backup in case 4 will be the same size as with 2 and 3, that is big.

Alen Cappelletti 992 Reputation points

2022-09-19T21:21:50.987+00:00

Thank you very much for your explain...
so closing... Is the backup size for filetable (4 point) the smallest?

'cause what confuse me are the varbinary(max) fields into system table for the ones of that kind... I read that are pointers to filesystem file... But seems to me very strange...

Alen Cappelletti 992 Reputation points

2022-09-19T21:32:23.677+00:00

Ok this Is the point... So Is great only for consistency!

Thanks now I got all the Infos... Only the First One if you want lightweight

Erland Sommarskog 110.4K Reputation points MVP

2022-09-19T21:32:29.407+00:00

The varbinary(MAX) column is the file. So even if the file is exposed in the file system, you can still retrieve the data with SELECT if you so fancy. And, as I recall, modify it with UPDATE. (It was quite a while since I played with the feature.)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

FileStream and filetable best minimum size for storing into DB

2 additional answers

Your answer