FileTable: Files You Can Open in Explorer and Query in T-SQL

A Windows folder can expose files that SQL Server also manages as rows. FileTable provides that shared namespace through FILESTREAM. It needs instance configuration and database storage prerequisites before the table can be created.

Check FILESTREAM Before Creating a FileTable

FileTable builds on FILESTREAM and adds nontransactional Windows access to a managed file hierarchy. This example requires a supported Windows SQL Server deployment. FILESTREAM must be enabled at instance level before anything else here works.

To enable it, open the service properties in SQL Server Configuration Manager. On the FILESTREAM tab, turn on Transact-SQL access and file I/O access with a Windows share name. Then set the filestream access level option to 2 and restart the service.

That is an administrator's setup decision. The demo reads the settings but doesn't change them. A plain CREATE TABLE cannot supply the missing instance prerequisites.

I check the configured and effective levels before touching the database. A requested setting and its active state can differ when a restart is pending. Confirm the Windows share configuration and required permissions too. If both values below come back 0, stop here, because every later block fails until that change is made.

My test instance returned 0 for both levels, so I syntax-checked the later blocks without running them. Use an isolated test database and approved directories. The feature connects database and file access, so both sides need a working configuration before the example is meaningful.

SELECT SERVERPROPERTY('FilestreamConfiguredLevel') AS ConfiguredLevel,
       SERVERPROPERTY('FilestreamEffectiveLevel') AS EffectiveLevel;

Provide a FILESTREAM Filegroup

The database needs a FILESTREAM filegroup and container. Ordinary data and log files still exist alongside it. The paths below are placeholders on the server.

Create the parent directory beforehand and grant the service account access. The FILESTREAM container directory itself must be created by SQL Server as part of this operation. Don't prepopulate it with ordinary files or use it as a shared scratch folder.

The sample names a separate database to keep the setup isolated. Review disk capacity and backup handling before creating it. A FILESTREAM container belongs to database-managed storage.

Copying arbitrary files directly into its physical structure bypasses the supported interface. Windows access for users comes through the managed namespace instead. Keep that namespace distinct from the underlying storage path when explaining where files should be opened.

CREATE DATABASE FileTableDemo
ON PRIMARY
(NAME = N'FileTableDemoData',FILENAME = N'C:\SqlFileTableDemo\FileTableDemo.mdf'),
FILEGROUP FileTableStorage CONTAINS FILESTREAM
(NAME = N'FileTableDemoStream',FILENAME = N'C:\SqlFileTableDemo\FileStore')
LOG ON
(NAME = N'FileTableDemoLog',FILENAME = N'C:\SqlFileTableDemo\FileTableDemo.ldf');

Enable the Database Namespace Deliberately

NON_TRANSACTED_ACCESS controls the database's Windows file access. FULL allows read and write through that namespace, while other choices restrict access. DIRECTORY_NAME supplies the database directory beneath the instance's configured FILESTREAM share.

Review the access boundary before enabling write access. A file operation can change stored content, so it belongs in the application's permission and audit design.

I confirm the effective database settings after the change. The catalog reports the directory and nontransactional access choice. Don't infer those values from the folder you hoped to create.

Database permissions and Windows authentication requirements still matter. The namespace makes access convenient. It doesn't remove the need to decide which accounts can read or alter the documents exposed through it.

ALTER DATABASE FileTableDemo
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = N'FileTableDemoFiles');
SELECT DB_NAME(database_id) AS DatabaseName,directory_name,non_transacted_access_desc
FROM sys.database_filestream_options
WHERE database_id = DB_ID(N'FileTableDemo');

Create the FileTable Without a Custom Column List

This table has a predefined schema. CREATE TABLE AS FILETABLE supplies those columns rather than accepting an ordinary custom definition. FILETABLE_DIRECTORY gives this table's directory name.

The filename collation controls Windows-style name comparisons and must be suitable for that purpose. Keep the predefined metadata contract separate from application attributes. Related ordinary tables can hold those attributes under the file identity.

The following block switches to the isolated database and creates its table. The directory is exposed beneath the database namespace. Use FileTableRootPath to obtain the root rather than constructing a share path from memory.

Open that returned path in Explorer under the appropriate authorized account. Put a harmless sample file there, then inspect the rows using T-SQL. File operations and database rows share the same managed content.

USE FileTableDemo;
GO
CREATE TABLE dbo.ManagedDocuments AS FILETABLE
WITH (FILETABLE_DIRECTORY = N'Documents',FILETABLE_COLLATE_FILENAME = Latin1_General_100_CI_AS);
SELECT FileTableRootPath(N'dbo.ManagedDocuments') AS FileTableRoot;

Read Names, Streams, and Locators

The name column stores the file or directory name. The file_stream column exposes the file's binary content through FILESTREAM. The path_locator column represents its place in the hierarchy using hierarchyid.

Other predefined columns describe directories, extensions, and timestamps. A directory row isn't an ordinary document payload. Filter is_directory when counting or examining files so folder metadata doesn't become a mysterious empty attachment in the report.

Use cached_file_size for a metadata size view and DATALENGTH when deliberately inspecting the exposed binary value. Reading content is a different workload from listing names. Don't SELECT every file_stream merely to build a document browser.

Return the metadata first and retrieve a selected payload when needed. The database can answer a file inventory without making the application carry every document across the network.

SELECT name,file_type,path_locator,cached_file_size,
       DATALENGTH(file_stream) AS ContentBytes
FROM dbo.ManagedDocuments
WHERE is_directory = 0 AND file_type = N'pdf'
ORDER BY name;

Include Files in Recovery Planning

The stored files belong to the database's FILESTREAM data and its recovery plan. Test backup and restore for the chosen deployment and access pattern. Coordinate open file access with maintenance.

Nontransactional Windows writes aren't the same application interaction as a T-SQL transaction covering business rows. Decide how metadata and content changes remain consistent when both interfaces are used by your application.

Which component owns a document rename or replacement? Define that before allowing several writers. An application table referencing stream_id needs a rule for deletion and movement too.

Don't equate a stable filename with a stable business identifier. The folder interface is familiar, but the application still needs ownership, retention, and authorization rules. Explorer isn't a substitute for a document lifecycle specification.

Choose a Path Column When a FileTable Is Too Much

The feature is useful when Windows file access and database-queryable metadata need to share managed content. A plain path column is simpler when the application already owns storage and only needs to record locations. That design shifts backup consistency and file lifecycle responsibilities outside SQL Server.

It also avoids the FILESTREAM setup and namespace requirements. Choose the boundary your team can operate reliably.

Keep the FileTable prerequisites visible in the runbook and verify the returned namespace on your own server. Test metadata queries, authorized file access, and recovery together. A familiar folder is inviting.

It still sits on a managed database feature with operational rules. Use the feature when the shared boundary solves a real requirement. Keep a simpler path design when it meets the need.

Related reading on this blog: Working with FileTables in SQL Server 2012: Part 3: Retrieving Various FileTable Properties and Working with FileTables in SQL Server 2012: Part 1: Setting Up Environment.

The managed namespace is not an ordinary shared folder, it is a file namespace backed by database-managed storage.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.