SQL Server FILETABLE is a next generation feature of SQL FILESTREAM. We can use it to store unstructured objects into a hierarchal directory structure. SQL Server manages SQL FILETABLE using computed columns and interacts with the OS using extended functions. We can manage SQL FILETABLEs similar to a relational table.

Before we proceed with this article, to follow along, please prepare the environment using my earlier articles (see TOC at bottom).

Prerequisites

• A SQL Server instance with FILESTREAM feature
• Configure filestream_access_level for T-SQL and Windows streaming
• A SQL FILETABLE database having SQL FILESTREAM filegroup
• A SQL FILETABLE

Explore a FILETABLE

Connect to a SQL instance and expand the FILETABLE database. Right click on SQL Server FILETABLE SQLShackDemoDocuments and click on Explore FileTable Directory.

You get the FILETABLE directory in the following format.

\\servername\instance-share\database-directory\FileTable-directory

In the SQL FILESTREAM table, we can insert records using an Insert statement. We can use an Insert statement for SQL Server FILETABLE as well.

Execute the following query to insert the object into FILETABLE. We need to only provide value of 2 columns [name] and [file_stream] in FILETABLE.

Select records from a FILETABLE and you can see in the following image, and all other columns are populated automatically using computed functions.

Now, go to the FILETABLE directory, and you can see the following file into it.

SQL FILESTREAM provides an internal value to every object and file. It is not easy to identify the FILESTREAM object by looking in the directory. It does not store file extension either. Windows does not recognize the FILESTREAM object because it is doesn’t have an extension associated with it. But a SQL Server FILETABLE does make it easy for us in the following ways.

• SQL FILETABLE stores the original object name and extension for each object
• Windows recognize these file. We can work with these files similar to a regular file
• The FILETABLE also maintains file properties and characteristics such as read-only

In the following screenshot, you can see the difference in SQL FILESTREAM and SQL FILETABLE objects in both naming convention and extensions.

Suppose we want to insert a large number of unstructured objects into a SQL FILETABLE. We can use custom stored procedure or SSIS packages to loop through all files in the source directory and insert into the SQL FILETABLE. We explored this method in my earlier article (see TOC at the bottom).

We can use a similar method for SQL FILETABLEs as well. In this article, we will cover a better approach with using the FILETABLE feature.

SQL Server FILETABLE allows dragging and dropping objects into SQL FILETABLE from the source folder. SQL Server interacts with these files using system functions and inserts records in SQL FILETABLE. Let us understand it using an example.

In the following screenshot, we performed drag and drop activity for 27 objects from source folder to FILETABLE directory.

It starts a copy task in Windows. You can see the status of copying each file as per the following screenshot.

Once the copy is completed, we can see all files in SQL Server FILETABLE directory.

SQL FILESTREAM also allows copying any file in a FILESTREAM container. It does not create an association of these files into FILESTREAM table. You also get an error message about the unwanted object while taking backup of FILESTREAM database.

SQL Server FILETABLE is aware of any activity in FILETABLE directory. It automatically reads each file and inserts a corresponding entry in SQL FILETABLE. In the following screenshot, we have an entry for each file we copied in SQL FILETABLE directory.

We can quickly move files around SQL FILETABLE directory without worrying about FILETABLE metadata.

For this demo, I created a folder image into SQL Server FILETABLE root directory. I moved all images into this folder.

If you look at the SQL FILETABLE directory, we have following parent-child relationship.

• Parent Directory: \\Kashish\sql2019\FileTableContainer\SQLShackDemo
• Child Directory: \\Kashish\sql2019\FileTableContainer\SQLShackDemo\Images

A FILETABLE maintains parent-child relationship using path_locator and parent_path_locator columns.

Now, query the SQL FILETABLE again. In the following screenshot, you will notice the following.

• Object Writing to SQL Server FILETABLE.xlsx is having path_locator value but does not have parent_path_locator

We have placed an object into a parent folder; therefore, it doesn’t have a parent associated with it.

• Further objects are having a parent_path_locator value equal to path_locator of the previous entry. We have created the images folder inside the parent folder (SQLShackDemo)

You can better understand our parent-child relationship association using SQL FILETABLEs with the following image.

We can easily remove objects from SQL Server FILETABLE similar to a regular windows file. I have deleted a few objects from the images folder. SQL Server removes the metadata from SQL FILETABLE automatically.

SQL FILESTREAM does not allow placement of objects directly in a FILESTREAM container. Suppose we want to update content in a Word document, in SQL FILESTREAM, we need to update it using an update query. SQL Server prepares a new copy of the object in FILESTREAM container and removes the old file using a garbage collector process if it not required by the recovery process. The garbage collector process depends upon your recovery model.

Updating a FILETABLE

Let us explore updating SQL Server FILETABLE. For this demo, I am going to create a new Microsoft Office Word Document. Right click on the desired directory, go to New and click on Microsoft Office Word Document.

I renamed this Word document to UpdateFILETABLE.docx.

Currently, it has no content, therefore you can see 0 KB size as well.

Open this Word document and place content inside it. I have placed following image in this and saved the doc. As stated earlier, We can do an in-place update to FILETABLE object directly. It removes a barrier with SQL FILESTREAM in which we require update query and create a new copy of the object. It requires more space as well to store files.

Once we saved Word document, you can query SQL Serve FILETABLE, and you will see modified document timestamp in the last_write_time column.

You can match this value with file modified time in the following screenshot.

Rename objects in a SQL FILETABLE

Suppose we want to rename an object in SQL Server FILETABLE directory. We can right click on that file and rename it.

In the following screenshot, I renamed highlighted image file.

Once we query FILETABLE, we can see modified name appearing in this list.

Now, let us change the properties of a particular file to Read-Only. Right click on the file and go to properties. Put a check on Read-Only and click Apply.

Select the record in SQL Server FILETABLE, and you can see the is_readonly flag is set to 1 for that file.

We can do it other ways as well. In the following query, we set read-only and archive properties for an image file.

Once you have executed this query, open file properties in FILESTREAM directory and we can see the changes at OS level as well.

Create a Folder using t-SQL

Previously we created a folder in SQL Server FILETABLE directory in OS level. We can also create a folder using t-SQL as well in FILETABLE directory.

In the following query, we are creating two directory SQLShack and DemoFolder. We need to specify an is_directory flag to 1 so that SQL Sever can create an appropriate directory in FILETABLE.

In the following screenshot, you can see newly created directories inside FILETABLE root directory.

Restrict a user from performing inserts on a FILETABLE

Suppose we want to restrict a SQL user from performing inserts on SQL Server FILETABLE. We can explicitly deny permissions to that user similar to a relational table.

First, let us create a SQL user and assign db_reader and db_writer permission on FILETABLE database.

Once user is created, run following query to deny permissions for Insert command.

Now, connect to SQL user with DemoLogin we created and try to run an insert command on a FILETABLE.

We get an error message that you do not have permissions to use the bulk load statement.

Similarly, we can control permissions to update, delete, select on SQL FILETABLE as per our requirements.

Identify the FILEPATH for a particular file

Suppose we have a large number of directories and files in SQL Server FILETABLE. We can search for a particular file using Windows Search; however, it is possible from t-SQL as well. We need to use system functions FileTableRootPath() and GetFileNamespacePath to retrieve relevant information.

In the following query, we want to identify the directory path for IMG_20140316_133802. We specified a variable @name to hold objectname. It is the name of the object we want to search in FILETABLE directory.

Execute the following query to get directory information for that particular file.

Important facts about SQL FILETABLE

• We can perform standard insert, update, delete on FILETABLE
• We can use DML and DDL triggers on the FILETABLE
• FILETABLE can be dropped similar to a relational table
• SQL Server ensures transactions consistency in FILETABLE
• We can use built-in functions to retrieve information about FILETABLE and interaction with OS
• We can use copy,robocopy and drag-drop operations through WindowsExplorer for direct T-SQL data inserts in a FILETABLE
• We can access objects in FILETABLE directory without authorization from the SQL Server

Conclusion

In this article, we talked about SQL FILETABLE features and its integration with the OS. We can use unstructured store data in the file system and keeps metadata in a database. FILETABLE allows non-transactional access. SQL Server ensures consistency as well for SQL Server FILETABLE. We will cover a few more aspects in my next article.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023