SQL 2012 Hosting :: SQL 2012 File Tables

FileTables is a new feature introduced in SQL Server 2012. It’s a unique table that reflects metadata of files in a specified folder. The file attributes can then be modified either through SQL queries or via Windows Explorer.

The two primary benefits of FileTables are the ability to dynamically access file attributes via DML, and to perform FullText search on files in the specified folder. The FileTable feature builds on FileStream and HiearchyID, which were introduced in SQL Server 2008. To demonstrate this feature, I’ll create a sample FileTable for storing data on cars.

Setup
In order to use FileTables, the following four steps must be completed, in order:
1. Enable FileStream at the instance Level
2. Provide a FileStream Filegroup
3. Enable Non-Transactional access and specify FileTable directory at the Database Level
4. Create a FileTable

Enable FileStream at the Instance Level
Enabling FileStream at the instance level can be performed in either of two ways. First, it can be configured through SQL Server Configuration Manager. Figure 1 shows a listing of all the SQL Server Services. Right-clicking on the name of the instance, selecting “Properties”, and the “FileStream” tab displays the various options for FileStream. For this demo, I decided to enable all options.

The second method of enabling FileStream is by using the stored procedure “sp_configure”. This stored procedure requires two parameters: “filestream_access_level” and the security level. For more information on Enabling FileStream, please see the MSDN documentation.

If you attempt running the sp_configure stored procedure prior to configuring it at the instance level, you’ll receive the following error:

Otherwise, when successful you’ll receive the following message:

Provide a FileStream Filegroup
The next step is to provide a FileStream FileGroup. This can be incorporated in the CREATE DATABASE command. Go here for additional information on the CREATE DATABASE command.

After executing the above statement, you’ll see the new additions to the “FileTablesDemo” folder, as shown below

Figure below shows a FileTable folder in Object Explorer that was created by default.

Enable Non-Transactional Access at the Database Level
Because FileTable allows modifications to be performed via Windows applications without requiring a transaction, I have to enable non-transactional access. To do so, I execute the SQL statement below. Note a directory name is specified — this is the directory that will store files used with the FileTable.

Create a FileTable

The final step for configuring FileTables is to create the FileTable itself. This is accomplished by executing a bit of SQL:

Note that there was no structure specified for the table. The FileTable was created using the default schema containing 17 fields, as shown below:

If you right-click the newly created FileTable (“CarsDocStore”) and select “Explore FileTable Directory”, a new window will open, directing you to the corresponding folder. The FileTable folder is created in the directory specified earlier.

Figure 5a

Now that the setup process is completed, the FileTable can be used in various ways. First, I’ll ¬†show an example of how a FileTable can manage files directly on the file system. Then I’ll show how it can be expanded upon for FullText search. This will allow any file added to the folder to be searched by T-SQL statements seamlessly.

Bilateral File Access

First, notice the folder in Figure 5a is empty. Likewise, when I query the table CarsDocStore, it returns 0 records, as seen in Figure 5b. This is because the FileTable is a direct reflection of the CarsDocStore folder.

Figure 5

For my CarsDocStore example, I created three .TXT files:

  • AudiA6.txt
  • BuickRegal.txt
  • FordMustangGT.txt

Each file contains the following items for each car:

  • Model Year
  • Mileage
  • Body Style
  • Engine
  • Exterior Color
  • Interior Color
  • Interior Material

Next, I simply drag the three files into the CarsDocStore folder, and manually create a sub-folder called “SUVs”. These items can be seen in Figure 6a. This will immediately populate the CarsDocStore table with file metadata, as evidenced by the query in Figure 6b.

Figure 6a

Note the difference among some of the fields for file records and the folder record, specifically file_stream, file_type, cached_file_size, is_directory, and is_archive.

Figure 6b

To further show the power of FileTables and its bilateral file access, file attributes modified in Windows Explorer will immediately be reflected in the FileTable and vice-versa. For example, executing the T-SQL here will modify the file “FordMustangGT.txt” to be read-only:

Likewise, a file can be completely deleted from the CarsDocStore folder:

Configuring FullText Search

Another key advantage of FileTables is the ability to utilize FullText searches. By configuring a FullText catalog on the FileTable, the files used in the FileTable directory can be searched using T-SQL. To configure FullText search, I executed the following statements:

The Primary key index specified in the Create FullText statement was obtained from Object Explorer. Since this key is created and named dynamically by default, it will have to be obtained for every FileTable created. Figure 7 shows the statements used for configuring FullText search, as well as a view of Object Explorer with the Primary Key highlighted.

Figure 7

Using FullText Search
After configuring FullText search, a T-SQL statement can be used to query the FileTable for specific text. Figure 8 shows a FullText search for the words near to “V6” and “Leather”.¬† This returns the record for the AudiA6.txt file. As mentioned previously, the FileTable is a direct reflection of the folder on the file system. Therefore, the FullText search is essentially searching the files in the folder using a simple T-SQL statement. By performing this query, I automatically searched all files in the folder and found AudiA6.txt to contain the words “V6” and “Leather”.

Figure 8

FileTables is one of the powerful features of SQL Server 2012. It allows T-SQL access to a shared folder seamlessly and with little configuration. In addition, everything pertaining to a file can be accessed bilaterally, either through the file system or T-SQL. By having a table dynamically updated with files stored in the shared folder, users can now access file content and metadata without complex firewall configurations.