Code Focused

Using FileTables in SQL Server 2012

FileTables, a new feature introduced in SQL Server 2012, is a unique table that reflects metadata of files in a specified folder.

• By Sam Nasr
• 10/25/2012

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.

[Click on image for larger view.]

Figure 1. Using SQL Server Configuration Manager to configure FileStream Access.

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.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Go

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

Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.
Msg 5591, Level 16, State 1, Line 2
FILESTREAM feature is disabled.

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

Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.

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.

CREATE DATABASE Cars ON PRIMARY (NAME = Cars_data, FILENAME = 'C:\FileTablesDemo\Cars.mdf'),
  FILEGROUP CarsFSGroup CONTAINS FILESTREAM (NAME = Cars_FS, FILENAME = 'C:\FileTablesDemo\CarsFileStream')
  LOG ON (NAME = 'Cars_log', FILENAME = 'C:\FileTablesDemo\Cars_log.ldf');        
GO

After executing the above statement, you'll see the new additions to the "FileTablesDemo" folder, as seen in Figure 2.

[Click on image for larger view.]

Figure 2. Folder structure, post-DB creation.

[Click on image for larger view.]

Figure 3. The FileTable folder 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.

ALTER DATABASE Cars
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'CarsDataFS')

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

Use Cars
Go

CREATE TABLE CarsDocStore AS FileTable
GO

Note that there was no structure specified for the table. The FileTable was created using the default schema containing 17 fields, as seen in Figure 4.

[Click on image for larger view.]

Figure 4. Default FileTable schema.

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 (as seen in Figure 5a). The FileTable folder is created in the directory specified earlier.

[Click on image for larger view.]

Figure 5a. The corresponding FileTable folder.

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.

[Click on image for larger view.]

Figure 5b. Initial query of the CarsDocStore table.

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.

[Click on image for larger view.]

Figure 6a. Results after copying files.

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.

[Click on image for larger view.]

Figure 6b. Query results after copying files.

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:

update dbo.CarsDocStore
set is_readonly = 1
where name = 'FordMustangGT.txt'

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

Delete 
from dbo.CarsDocStore
where name = 'BuickRegal.txt'

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:

--Create Full-Text Catalog
Create FullText Catalog MyFullTextCatalog as Default
GO

--Create FullText Index 
Create FullText Index on dbo.CarsDocStore
(name Language 1033, File_stream type column file_type Language 1033)
key Index PK__CarsDocS__5A5B77D53B0833DF
on MyFullTextCatalog
with Change_Tracking Auto, StopList=system
Go

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.

[Click on image for larger view.]

Figure 7. Configuring FullText search.

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". More information on using the "near" keyword is available here.

[Click on image for larger view.]

Figure 8. Using FullText search query.

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.

• Get Code Download