Sql-server – How does SQL bind rows in filestream-enabled table with their files

filestreamsql-server-2008-r2

Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table.

For example, I have the Images table which is filestream-enabled, it have a filestream column called Data its type : VARBINARY(MAX).

Here is an example of that table that have 19 rows and also the filestream folder contains 19 rows :
an example of that table that have 19 rows and also the folder contains 19 rows

Is it stored in some system table, or there is direct relationship between the "ROWGUIDCOL" and its name ?

Best Answer

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database.

Below are a few good links on the architecture:
Paul Randal White Paper - FILESTREAM Storage
Paul Randal Blog Post - FILESTREAM directory structure

As quoted from Paul's blog post above:

The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. You can correlate these by looking at the data with DBCC PAGE, but first finding the allocated pages using sp_AllocationMetadata (see this blog post).

Note the stored procedure he mentions is an undocumented one that he provides some additional information to in the blog post he linked.

Related Question