SQL Server misunderstanding: 18th-day discussion about FileStream storage, garbage collection, and other

Misunderstanding #18: Several misunderstandings related to FileStream

All Errors

18 a) FileStream data can be stored remotely

No, because the FileStream data container (which refers to the NTFS folder that stores the FileStream file, the fabricated term) must comply with the local storage policy as the data file or log file-that is, this data container must be stored locally on the Windows Server running SQL Server. (Note: The storage can be seen in the 'computer '. Of course, DAC is, in fact, if SAN is not directly connected to the Server, you can access FileStream data as long as the client connects to the SQL Server and obtains the response transaction context.

18 B) FileStream data containers can be nested

No. For two different FileStream containers in the same database, they may be in the same directory, but they cannot be nested. The FileStream containers of different databases cannot be in the same directory. One of my blog posts has a piece of code to illustrate this: Misconceptions around FILESTREAM storage.

18 c) FileStream updates can be partially updated.

Any FileStream update will create a new FileStream file. This operation will be recorded in the original log. This is why FileStream cannot be used for database images. If so much data is used in an image, the consequences are simply unimaginable. We can only hope that in the future, SQL Server versions can modify this mechanism to allow partial updates.

18 d) FileStream will be immediately reclaimed when it is not needed

Error. FileStream data is garbage collected when it is no longer needed and reaches the next Checkpoint. This is not so direct that many people have misunderstandings about the FileStream recycling mechanism.

18 f) The FileStream directory and file name are randomly obtained.

Otherwise, the file name of FileStream actually represents the LSN corresponding to the creation operation. The GUID Directory Name of the table and column can be obtained in the system table.

The following two blog posts provide more detailed explanations:

• FILESTREAM directory structure explains how to know the name of a FileStream from its row

• FILESTREAM directory structure-where do the GUIDs come from? You can understand the content of this article as soon as possible :-)