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:
|
1 2 3 |
-- 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:
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
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:\SQLData\SampleDB\fs_documents' ) TO FILEGROUP fs_documents; |
Gotcha: The path you specify in
FILENAMEmust 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.
|
1 2 3 4 5 6 |
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:
|
1 |
SELECT FileTableRootPath('dbo.PDFDocuments') AS UNC_Root; |
You’ll get something like:
|
1 |
\\YOURSERVER\MSSQLSERVER\SampleDB\PDFDocuments |
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., SERVER\INST01), the UNC path format will be
\\SERVER\INST01\SampleDB\PDFDocuments.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:\Temp\Q1_Report_2025.pdf', SINGLE_BLOB ) AS doc; |
Option B: Insert binary content directly
|
1 2 3 4 5 6 7 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 |
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
|
1 2 |
bcp "SELECT file_stream FROM SampleDB.dbo.PDFDocuments WHERE name = 'Q1_Report_2025.pdf'" ^ queryout "C:\Output\Q1_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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @pathLoc HIERARCHYID; INSERT INTO dbo.PDFDocuments (name, file_stream, is_directory) SELECT N'Contract_2025.pdf', BulkColumn, 0 FROM OPENROWSET(BULK N'C:\Temp\Contract_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 FILETABLEstatement will fail. - FILESTREAM filegroups need explicit backup coverage. A standard
BACKUP DATABASEagainst 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.
