SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FILESTREAM Data


FILESTREAM Data

Author
Message
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 143
Comments posted to this topic are about the item FILESTREAM Data
hakan.winther
hakan.winther
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3349 Visits: 612
I think this is an Incorrect question! How do you update the FILESTREAM column without updating the row?

Håkan Winther

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19201 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ian_McCann
Ian_McCann
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 2332
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

http://msdn.microsoft.com/en-us/library/cc949109.aspx

states
"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

Ian
Steven Cameron
Steven Cameron
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2215 Visits: 215
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.
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 143
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.

):-D
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16329 Visits: 25280
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.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151174 Visits: 19455
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steven Pearson
Steven Pearson
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 765
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."
DPhillips-731960
DPhillips-731960
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 801
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search