• I have to disagree with half of the answer key.

    First: The third statement ("when data in a FILESTREAM column is updated, the data in the filestream file is updated") is of course true. Otherwise, the updates would have been lost. How the update is performed (only rewrite changed bytes or rewrote complete content) is an implementation detail. From the answer key, I gather the intent was to check if we know that particular implementation detail, but that was far from clear from the question. Deleting and rewriting the content is a way to update the data in the filestream file, after all. If the statement would have been written as "when data in a FILESTREAM column is partially updated, only the modified data in the filestream file is updated", then I would have agreed.

    Second: The second statement ("the NTFS partition that holds filestream data should have generation of 8.3 names and last access time tracking disabled") is ambiguous, at best. I'm not a native English speaker, but as far as I know "should" can have two meanings in this context: "must", or "recommended". With the first interpretation, this statement is completely false - FILESTREAM will work just fine without disabling these Windows options. With the second interpretation, it's less clear cut, but I still find the statement too strong. The white paper the answer key references recommends disabling these options for performance, but (a) only if the amount of files in the directory becomes very large (300,000 or more - easily achieved if a FILESTREAM column is in the "Customers" table, but defnitely not if it's in the "Products" or "Branches" table...), and (b) not if there are other applications running on the same box that require 8.3 filenames.

    So either you interpret "should" as "must", in which case the second statement is false, or you interpret "should" as "recommended", in which case the second statement is a somewhat good, but too strong recommendation that lacks the imporant caveats, so still false.

    The correct answer to this QotD is that the first, third and fourth statements are true, and the second statement is false. Which is what (at this time) the majority of respondents have answered.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/