What is the proper way to handle file sizes when using Sql Server's FILESTREAM feature to store documents in the database?
I know that you can use the TSQL command DATALENGTH() on the filestream column, but regarding to this Microsoft article this might not always be the best idea:
Avoid retrieving the data length of lots of BLOB files in an application. This is a time-consuming operation because the size is not stored in the SQL Server Database Engine. If you must determine the length of a BLOB file, use the Transact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH() does not open the BLOB file to determine its size.
I am a bit unclear if the above says that you should avoid DATALENGTH() if possible or if it is the recommended way to retrieve the file size.
If I have to access document sizes often to show it to the user as metadata, what is the recommended way to retrieve them? Store them in a separate field? Or is datalength() fast enough?
I believe the article is suggesting not to use DATALENGTH() if it can be avoided.
Storing the file size in a separate column is a suitable alternative, along with other metadata about the files (content type, extension, timestamp, etc). This will allow you to query/filter file details more efficiently.
If the file contents are not expected to change, simply updating a filesize column while persisting the filestream data is simplest.
If the contents are expected to change, you need to remember to update the file size as well. This can also be accomplished in Sql with a persisted computed column