• Comments posted to this topic are about the item FILESTREAM Data

  • I think this is an Incorrect question! How do you update the FILESTREAM column without updating the row?

    Håkan Winther

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • 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:
    SQL Server Execution Plan Reference:

  • I agree with the first point the question is badly structured and I clear as to what was being asked. However point two, I'm not so sure about


    "There are two NTFS options that need to be configured to facilitate FILESTREAM performance....

    The first configuration option is to disable the generation of 8.3 names when new files are created (or renamed)."

    should != must


  • I agree with Hugo that whether you update part of a file or re-write the entire file, the data in the file is updated. The bits may be in a different location, but it is the same file.

  • I apologize for not being able to give this forum the attention you all deserve.

    My Mother is in the hospital and gets out today. I must bring her to my house.


  • From the Encarata dictionary

    Update (transitive verb)

    to provide somebody or something with the most recent information or with more recent information than was previously available

    The data in the filestream file IS for all practical purposes changed i.e., updated. What is the mechanism of how that task is accomplished, is the real question we are quibbling about

    It is accomplished by writing the new content to a new file, but this new files file name / identification is NOT changed.

    My heart goes out to Dr. Diana Dee and Steve Jones, as I well know that framing the QOD is not a simple task, and I do disagree with the questioners designation of which answer is incorrect

    If everything seems to be going well, you have obviously overlooked something.


    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I have updated the question to mark #3 as correct. Not sure how I missed that.

    I disagree with Hugu. In English, to me, a "should" implies a recommendation. That would fit with recommendations I've seen in that white paper.

    I have returned points to people answering up to this point.

  • I disagree with the inclusion of #3 being correct. The "FILESTREAM file" is not updated but is replaced with a new file.

    from the referenced artical:

    "Whenever a FILESTREAM file is opened for a write operation, a new zero-length file is created and the entire updated FILESTREAM data value is written to it. The old file is removed asynchronously as described in the “Configuring FILESTREAM Garbage Collection” section later in this white paper."

  • Having not had the chance or reason yet to use FileStream data in SQL Server, I found the question and the discussion enlightening, and intriguing. Thanks!

  • I'm sorry I didn't see this question yesterday...

    Back in 2003/4 I was one of the SDE/T's at Microsoft testing the new Filestreams feature in Yukon (2005). One of my responsibilities was to test the performance of the filesystem, to see what would happen when SQL server started generating large numbers of files in a directory. At the time, the Microsoft standard line was they did not recommend putting more than ~100,000 files in a single directory. I wrote programs that created, deleted, and did random seeks on directories with millions of files.

    I was actually the guy who made the recommendation to suggest turning off 8.3 names, after my testing found that filesystem performance got exponentially worse after only a few hundred thousand files.

    I remember the windows guys were shocked when I told them I wrote a program that generated over 20 million files in a single directory, with only slight linear performance degradation.

    Ahh, the memories.

  • I disagree with author!

    second answer it is recommendation for optimization ntfs perfomance.

    in isn't necessary.

    MS SQL 2008 MCITP x 3
    MS SQL 2012 MCSE x 2

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply