Exploring File tables and Managing files inside the file tables?

Problem: Exploring File tables and Managing files inside the file tables?

Solution: Manage FileTables

To know more about how to create a file table please go through this article. Create a File Table

Some common questions people are shy to ask about file tables are

1.     Where does this file table store the files in the drive?

A)    We can explore the file tables directory by right clicking on the file table to see the data directory

2.     How to know whether a file table exists?

A)    System object catalog views has a flag for file tables.

Sys.filetables or sys.tables(isFiletable=1)

3.     How different is this from the file stream?

A)    File and hierarchy directory are maintained in the database.

4.     When a file is removed from the folder will the table get updated?

A)    Once the files from drive are removed immediately NTFS will send the file updates to the table and adds/removes entries accordingly.

Example:

In this Example, I would like to show we update information in a filetable on a file stream enabled database.

I have file in the filetable directory as file table 2.png and I will update the file name using a DML statement to update the table row and with the help of stream_id to identify the file.

BEFORE:

 UPDATE

[dbo].[FT_Document]  SET

name = 'updatefilename.png' WHERE

stream_id = '55323FBC-D531-E711-829D-847BEB2C90E9'

AFTER:

 

So as the above update command was able to modify the file name file tables have full control on the files in the particular directory.