The File Table uses the technology of File Stream. It keeps file system data as a table.
That is, you can access the data on disk from within the database.
Unlike File Stream, it keeps the directory hierarchy in the database.
The schema structure is not defined in the file table. In the file stream, you define the schema structure.
The schema structure occurs automatically and can not be changed.
The file table can also be viewed as a share via windows.
You need to enable Enable FILESTREAM for file I/O access via Configuration Manager.
By reading the article titled “What is File Stream On SQL Server“, you can find out how to make this setting on the configuration manager.
This feature can be enabled at the database level.
In the File Table you can access the file properties (create date, modified date) via the database.
As with File Stream, it allows file directories and files to be queried with tsql.
You can use File Stream and File Table together.
File Table does not support memory-mapped files.
For example, notepad and paint uses memory mapped files.
You can not use these applications with the file table on the same server.
I think we can understand File Table more clearly with an example.
First, create a database named FileTableDB.
To create a database, you can read the article named “How To Create a Database On SQL Server“.
Then you can enable File Stream.
We need to apply the same steps as “What is File Stream On SQL Server“.
After we arrive at the table creation section, our steps change.
Right click on the database we created and click New File Table … as follows.
It will give you a script. Instead of using this script, we create our file table using the prepared script as below.
|
1 2 3 4 5 6 7 |
CREATE TABLE FileTableExample AS FileTable WITH ( FileTable_Directory = 'FileTableExample', FileTable_Collate_Filename = database_default ); GO |
If you do not specify the NON_TRANSACTED_ACCESS method and DIRECTORY_NAME for the File Table while creating the database, you will get an error like this:
Msg 33414, Level 16, State 1, Line 2
FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL.
To create a FileTable in the database ”, set the DIRECTORY_NAME option to a non-NULL value using ALTER DATABASE.
Or, to set the DIRECTORY_NAME option to NULL, in the database ” disable or drop the existing FileTables.
To resolve this error you should run the following script.
|
1 2 |
--READ_ONLY or OFF options are available for NON_TRANSACTED_ACCESS ALTER DATABASE FileTableDB SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableExample') |
If you get an error when you run the script, you can make the following changes on script.
|
1 2 3 4 5 6 |
ALTER DATABASE FileTableDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO --READ_ONLY or OFF options are available for NON_TRANSACTED_ACCESS ALTER DATABASE FileTableDB SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableExample') GO ALTER DATABASE FileTableDB SET MULTI_USER WITH ROLLBACK IMMEDIATE |
If you run the script again, you can create your table this time without any errors.
You can query the file table as below.
Query will not return a result because it is empty.
If you click on “Explore File Table Directory” by right clicking on the file table as below, you will go to the directory created for File Table.
When you create something in this directory and select it again, you will see that you create a row in the table for each folder and file.
