Rajendra Gupta
SQL FILETABLE root folder structure

Managing Data in SQL Server FILETABLEs

March 22, 2019 by

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

SQL FILETABLE root folder structure

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.

Explore SQL Server FILETABLE in SSMS

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

Explore SQL Server FILETABLE in directory

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.

Comparison of SQL FILESTREAM and SQL Sever FILETABLE

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.

Drag file into SQL Server FILETABLE directory

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

Copy Files in SQL Server FILETABLE

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

Files in SQL Server FILETABLE

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.

SQL Server FILETABLE records

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.

Move files into a new directory for SQL Server FILETABLE

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)

Parent-child relationsip in SQL Server FILETABLE

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 Server FILETABLE

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.

Create new file in FILETABLE directory

I renamed this Word document to UpdateFILETABLE.docx.

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

Create new file in FILETABLE directory

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.

Create new file in FILETABLE directory

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

Modified document timestamp in the last_write_time column.

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

Modified file properties in OS level

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.

Rename object in SQL Server FILETABLE

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

Rename object in SQL Server FILETABLE

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.

Read-Only properties of a file

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

Read-Only properties of a file in SQL Server FILETABLE

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.

Modified properties of SQL Server FILETABLE

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.

Create Folder using SQL

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.

Permission message

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.

Identify FILEPATH for a 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

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

268 Views