Sql-server – The MS documentation on FILESTREAM makes it sound like it should only be used for archiving unstructured data. Am I misunderstanding this

filestreamsql server

I am building an application that will store small image files (probably less than 1 MB typically) associated with records. I am considering the FILESTREAM option in MSSQL, but I'm a little confused about the documentation.

The documentation at Access FILESTREAM Data with Transact-SQL states:

You can use Transact-SQL to update the data in the file system file; although, you might not want to do this when you have to stream large amounts of data to a file.

If I need to update a file with a new version of it, what other option is there other than using the T-SQL "Update" command?

The documentation at Create Client Applications for FILESTREAM Data even goes further and says you shouldn't Insert or Delete data in situations where the filestream column can be null:

Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application's performance to decrease over time.

If you shouldn't insert or delete files in a table where the filestream can be null, what do you do in situations where not all records will have a file but there's a lot of transactions (including inserting and deleting) going on with that table? I mean, how else are you going to insert data to later read without…well…inserting it?

It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

I know I must be missing something, but the documentation seems a little confusing to me.

Is there a better tutorial or documentation out there for FILESTREAM?

Best Answer

Your understanding of the feature is correct.

It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

Yes. However, you're welcome to perform updates and deletes, keeping in mind that it can have a negative effect on performance.

For your use case, with small files of around 1 MB in size, you probably won't run into the issue as quickly.

You can also check out FileTable, which exposes an SMB share via FILESTREAM, if you're going to be doing modifications to the images.

Related Question