FileTables in SQL server

A FileTable is a special table built on top of FILESTREAM that Stores files & folders inside SQL Server But exposes them directly through Windows File System Meaning we can Insert files via SQL or drag-drop in Windows Explorer or SQL Server keeps them in sync automatically. It is a bridge between SQL Server relational engine and NTFS file system.

In other word we can say that FileTable is a SQL Server feature built on FILESTREAM that allows storing files and directories in a special table while exposing them directly through the Windows file system. Internally, SQL Server stores file metadata in relational format and the actual file content in NTFS outside the MDF file, ensuring transactional consistency between SQL operations and file system access. It supports folder hierarchy, native file operations like copy and delete, and can be queried using T-SQL like a normal table.

See the demo

Prerequisites for this table

Ø  Enable FILESTREAM

Ø  Restart the SQL Service.

Enable FILESTREAM

EXEC sp_configure 'filestream access level', 2; RECONFIGURE;

 Create database with FILESTREAM

CREATE DATABASE TestDB ON PRIMARY (     NAME = TestDB_Data,     FILENAME = 'D:\Data\TestDB.mdf' ), FILEGROUP FileStreamFG CONTAINS FILESTREAM (     NAME = TestDB_FS,     FILENAME = 'D:\Data\TestDB_FS' ) LOG ON (     NAME = TestDB_Log,     FILENAME = 'D:\Data\TestDB.ldf' ) WITH FILESTREAM (     NON_TRANSACTED_ACCESS = FULL,     DIRECTORY_NAME = 'Docs' );

Database created

Sometime after running the above setting and restarting the SQL server but still getting the below error

FILESTREAM feature is disabled when we are creating the database.

To enable this, go to the SQL server Configuration Manager and select the server instance. First we need to stop this server.

Select this server and go to the properties.

Select FILESTREAM and enable the check.

 

Click apply and then ok.

Then we are able to create the database.

Enable FileTable in DB

ALTER DATABASE FileTableDemo SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );

 Create FileTable

CREATE TABLE MyFileTable AS FileTable WITH (     FileTable_Directory = 'Docs',     FileTable_Collate_Filename = database_default ); GO

Access from Windows

Open it \\localhost\MSSQLSERVER\Docs\Docs

Now we can

Ø  Drag files

Ø  Create folders

Ø  Delete files

SQL Server tracks everything.

We have opened this path and copying one file

Now running the below query.

Query files from SQL

SELECT     name,     file_type,     creation_time,     last_write_time,     file_stream.PathName() AS FilePath FROM MyFileTable;

Insert file via SQL

INSERT INTO MyFileTable (name, file_stream) SELECT     'SQL_Server_Version_stores.docx',     BulkColumn FROM OPENROWSET(     BULK 'D:\SQL_Server_Version_stores.docx',     SINGLE_BLOB ) AS FileData;

It is working on only windows. not working on UNIX or another Platform.

One row inserted successfully.

See the file in directory

We can see this information using this table

 We can store any kind of files like doc, txt,jpge, mp4 , audio or video files.

See in the table