How to create and use SQL Server FileTables: step by step

João Barros 29 de October de 2026 4 min read

Storing and managing files (documents, images) in a database can simplify backups and permissions. FileTables in SQL Server allow storing files in the file system while managing metadata and permissions from SQL Server, ideal for applications that mix relational data and files.

Prerequisites

SQL Server 2012 instance or later with FILESTREAM enabled.
Administrator permissions on the server to change SQL Server configuration and create folders on the file server.
SQL Server Management Studio (SSMS) or a T-SQL client.

Step 1: Enable FILESTREAM at the server level

FileTables depend on FILESTREAM. First enable FILESTREAM in the server properties and at the service level. In SSMS: click the server name → Properties → Advanced → enable "Enable FILESTREAM for Transact-SQL access" and, if needed, "Allow remote client access". Then restart the SQL Server service.

Step 2: Configure the server with T-SQL (optional)

It is also possible to configure with T-SQL to ensure it is active and configured for Transact-SQL and File I/O access.

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'filestream access level', 2; -- 0=off,1=T-SQL,2=Full access
RECONFIGURE;

Step 3: Create a database with FILESTREAM and FileTable

Create a database with a FILESTREAM filegroup pointing to a folder. This defines where the files will be stored on the file server.

CREATE DATABASE DemoFileTable
ON PRIMARY (NAME = DemoFileTable_data, FILENAME = 'C:\SQLData\DemoFileTable.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM (NAME = DemoFileTable_filestream, FILENAME = 'C:\SQLData\FileStream')
LOG ON (NAME = DemoFileTable_log, FILENAME = 'C:\SQLData\DemoFileTable_log.ldf');
GO

Step 4: Create a FileTable

A FileTable works like a normal table, with fixed columns and a link to the file system. You must enable some options and create the table with the AS FileTable option.

USE DemoFileTable;
GO

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
-- Ensure FILESTREAM is available in the database
ALTER DATABASE CURRENT SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL);
GO

CREATE TABLE Docs AS FileTable
WITH (FileTable_Directory = 'DocsDirectory', FileTable_Collate_Filename = DATABASE_DEFAULT);
GO

Step 5: Insert and access files (example)

You can insert files via T-SQL (using Openrowset BULK to load) or copy directly to the FileTable folder on the server. T-SQL example to insert metadata and a file:

-- Insert a binary file as varbinary and then update to the FileTable
-- Simplest method: copy the file to the FileTable folder on the server

-- Example reading metadata from the FileTable
SELECT file_stream, name, file_type, creation_time
FROM Docs
WHERE name = 'exemplo.txt';

Step 6: Access files via the file system

Each FileTable exposes a folder on the server. On the SQL server, navigate to the configured folder (e.g.: C:\SQLData\FileStream\DocsDirectory). Copy files into the folder and they appear as records in the table; deleting a record deletes the file.

Verify the result

Confirm that the table contains records and that the files appear in the file system folder. Run:

SELECT TOP 10 stream_id, name, file_type, file_stream.PathName() AS FilePath
FROM Docs;

Check the physical folder that the listed files exist. If they don't appear, check common errors: FILESTREAM not enabled, folder permissions, or incorrectly defined filegroup.

Conclusion

FileTables make it easy to manage files and metadata in SQL Server, facilitating centralized backups and permissions. Next steps: implement NTFS permission control for the FileTable folder, test performance, and integrate with applications that read files directly from the folder. Tip: try copying a large file and observe how SQL Server generates a record in the table — this helps understand FileTable/FS synchronization.