Beyond VARBINARY: How to Store PDFs in SQL Server Using FILESTREAM and FileTable

Hello, dear blog reader. Today’s post is coming to you straight from the home office, ready to talk about a topic that comes up more than you’d think: storing PDF documents inside SQL Server.

Whether it’s invoices, reports, scanned forms, or contracts, applications often need somewhere to park files. You could store them on a network share and hope nothing breaks the link, or you could pull them fully into SQL Server where they live alongside your data. In this post, I’m going to walk you through setting up FILESTREAM and FileTable in SQL Server, a feature set that gives you the best of both worlds: transactional integrity from SQL Server and file system performance from NTFS.

What is FILESTREAM / FileTable?

FILESTREAM lets SQL Server store large binary objects, such as PDFs, on the NTFS file system while keeping them under SQL Server’s transactional control. Think of it as SQL Server saying, “I’ll manage that file, but NTFS is going to do the heavy lifting on I/O.”

FileTable is built on top of FILESTREAM and takes things a step further. It exposes your files through a special SQL Server table and a Windows UNC share simultaneously. Your application can access files through T-SQL, and Windows Explorer can browse the same files through a network path. Pretty handy.

Step 1: Enable FILESTREAM at the Instance Level

Before anything else, FILESTREAM needs to be enabled at two levels: the Windows service level (done via SQL Server Configuration Manager) and the SQL Server instance level. For this walkthrough, we’ll focus on the instance level, assuming the service is already configured.

Run this in SSMS:

-- Level 2 enables both T-SQL access and Win32 streaming I/O (required for FileTable) 
EXEC sp_configure 'filestream access level', 2; 
RECONFIGURE;

Verify it took:

SELECT value_in_use
FROM sys.configurations
WHERE name = 'filestream access level';
-- Should return 2

Note: If you haven’t enabled FILESTREAM at the Windows service level yet, open SQL Server Configuration Manager ? SQL Server Services ? right-click your instance ? Properties ? FILESTREAM tab and check all three boxes.

Step 2: Add a FILESTREAM Filegroup to the Database

A FILESTREAM filegroup tells SQL Server where on disk the files should live. This is separate from your regular MDF/NDF data files.

USE master; 
GO 
-- Add the FILESTREAM filegroup 
ALTER DATABASE SampleDB 
ADD FILEGROUP fs_documents CONTAINS FILESTREAM; 
-- Point it to a directory on disk 
-- Important: This directory must NOT exist yet — SQL Server creates it 
ALTER DATABASE SampleDB 
ADD FILE ( NAME = N'fs_documents_data', FILENAME = N'C:SQLDataSampleDBfs_documents' )
TO FILEGROUP fs_documents;

Gotcha: The path you specify in FILENAME must not already exist. SQL Server will create it. If the folder is already there, you’ll get an error.

Step 4: Create the FileTable

Here’s where things get interesting. When you create a FileTable, you don’t define columns yourself. SQL Server will create all the required columns automatically. The schema is fixed by design.

USE SampleDB; 
GO 
CREATE TABLE dbo.PDFDocuments AS FILETABLE 
WITH ( 
     FILETABLE_DIRECTORY = N'PDFDocuments'
     , FILETABLE_COLLATE_FILENAME = database_default );

SQL Server will automatically create columns, including file_stream (the binary content), name (the file name), path_locator (a HIERARCHYID that identifies the file’s position in the folder structure), and several file attribute columns like creation_time, last_write_time, and cached_file_size.

Step 5: Find Your UNC Path

Once the FileTable is created, you can look up the UNC path that Windows will use to access it:

SELECT FileTableRootPath('dbo.PDFDocuments') AS UNC_Root;

You’ll get something like:

\YOURSERVERMSSQLSERVERSampleDBPDFDocuments

Open that path in Windows Explorer, and you’ll see your FileTable just like a regular shared folder. You can even drag and drop files right into it. SQL Server handles the rest.

Named Instances: If your SQL Server is a named instance (e.g., SERVERINST01), the UNC path format will be \SERVERINST01SampleDBPDFDocuments.

Step 6: Inserting PDFs via T-SQL

You have two main options for inserting files: loading from disk using OPENROWSET, or inserting binary data directly if your application is passing it through.

Option A: Load from disk using OPENROWSET

USE SampleDB;
GO

INSERT INTO dbo.PDFDocuments (name, file_stream, is_directory)
SELECT
    N'Q1_Report_2025.pdf',
    BulkColumn,
    0                           -- 0 = file, 1 = directory
FROM OPENROWSET(
    BULK N'C:TempQ1_Report_2025.pdf',
    SINGLE_BLOB
) AS doc;

Option B: Insert binary content directly

-- If your application is passing the file content as a varbinary parameter
INSERT INTO dbo.PDFDocuments (name, file_stream, is_directory)
VALUES (
    N'Invoice_00123.pdf',
    @pdfBinaryData,             -- VARBINARY(MAX) from your application
    0
);

Step 7: Retrieving Records

Querying a FileTable works just like any other table.

List all files with their UNC paths

SELECT
    name,
    cached_file_size                                AS file_size_bytes,
    creation_time,
    last_write_time,
    FileTableRootPath('dbo.PDFDocuments')
        + file_stream.GetFileNamespacePath()        AS full_unc_path
FROM dbo.PDFDocuments
WHERE is_directory = 0
ORDER BY creation_time DESC;

Retrieve binary content for a specific file

SELECT
    name,
    file_stream                                     AS pdf_binary
FROM dbo.PDFDocuments
WHERE name = N'Q1_Report_2025.pdf';

Export a file back to disk using BCP

bcp "SELECT file_stream FROM SampleDB.dbo.PDFDocuments WHERE name = 'Q1_Report_2025.pdf'" ^
    queryout "C:OutputQ1_Report_2025.pdf" -T -S YOURSERVER -f format.fmt

Step 8: Adding Metadata with a Companion Table

Here’s a limitation worth knowing upfront: FileTable does not support adding custom columns. The schema is fixed. So if you need to track things like who uploaded a file, what document type it is, or which application it belongs to, the standard pattern is a companion table that references the FileTable via the path_locator column.

CREATE TABLE dbo.PDFDocuments_Meta (
    meta_id         INT IDENTITY        PRIMARY KEY,
    file_name       NVARCHAR(255)       NOT NULL,
    path_locator    HIERARCHYID         NOT NULL,   -- References the FileTable row
    uploaded_by     NVARCHAR(100),
    document_type   NVARCHAR(50),
    upload_date     DATETIME2           DEFAULT SYSDATETIME(),
    CONSTRAINT FK_PDFMeta_FileTable
        FOREIGN KEY (path_locator)
        REFERENCES dbo.PDFDocuments (path_locator)
);

When inserting a file, capture the path_locator from the FileTable insert and write a corresponding row to the metadata table:

DECLARE @pathLoc HIERARCHYID;

INSERT INTO dbo.PDFDocuments (name, file_stream, is_directory)
SELECT N'Contract_2025.pdf', BulkColumn, 0
FROM OPENROWSET(BULK N'C:TempContract_2025.pdf', SINGLE_BLOB) AS doc;

-- Capture the path_locator just inserted
SELECT @pathLoc = path_locator
FROM dbo.PDFDocuments
WHERE name = N'Contract_2025.pdf';

-- Insert metadata
INSERT INTO dbo.PDFDocuments_Meta (file_name, path_locator, uploaded_by, document_type)
VALUES (N'Contract_2025.pdf', @pathLoc, N'gbargsley', N'Contract');

Things to Watch Out For

Before you go spin this up in production, here are a few gotchas I want to call out:

• The FILESTREAM directory must not exist before ALTER DATABASE. SQL Server creates it. If it’s already there, you’ll get an error.
• NON_TRANSACTED_ACCESS = FULL is required for FileTable. Without it, the CREATE TABLE AS FILETABLE statement will fail.
• FILESTREAM filegroups need explicit backup coverage. A standard BACKUP DATABASE against the primary filegroup only won’t capture FILESTREAM data. Make sure your backup jobs are backing up all filegroups.
• FileTable doesn’t support custom columns. Use a companion table (as shown above) for any metadata you need to track.
• Watch your disk space. PDFs add up fast. The FILESTREAM data lives outside your MDF/NDF files, so monitor the FILESTREAM directory separately from your regular data drive utilization.

Wrapping Up

FILESTREAM and FileTable are genuinely useful when you need to keep files tightly coupled with your relational data and still want solid I/O performance. The transactional consistency alone makes it worth considering over a plain network share, no more orphaned files or broken references when a transaction rolls back.

The setup has a few moving parts, but once it’s running, it’s pretty seamless. Your applications can hit it through T-SQL, and your operations team can browse it through Windows Explorer like any other share. That’s a nice combination.

Give it a try in a dev environment first, get comfortable with the backup implications, and then bring it to production. As always, if you run into any issues or have questions, drop them in the comments below.

See you on the next one.

The post Beyond VARBINARY: How to Store PDFs in SQL Server Using FILESTREAM and FileTable appeared first on GarryBargsley.com.