Where should SQL Server store files like PDFs, images, and scanned records? It’s a decision that quietly shapes backup size, restore times, and how much memory is left for queries.
SQL Server file storage involves a few potential approaches: storing files as varbinary(max) BLOBs inside the database, using FILESTREAM (and FileTable, which builds on it), or keeping files outside the database and storing only a path. Object size and access pattern decide which fits. Microsoft recommends FILESTREAM when objects average more than 1MB, while smaller objects usually stream better as in-database BLOBs.
This guide explains how each option works, the trade-offs between them, and why the storage layer underneath is what ultimately determines whether any of them performs well.
How SQL Server stores data and where large objects fit
Every SQL Server database is made up of at least two operating system files: a data file and a transaction log file. The primary data file, conventionally .mdf, holds the database’s startup information and points to the other files in the database. A database can also have secondary data files, conventionally .ndf. Administrators add these for reasons such as managing file size and growth, enabling filegroup-level backup and restore, or reducing allocation contention in tempdb.
The transaction log, conventionally .ldf, records the changes needed to recover the database. A database can technically have more than one log file, but SQL Server writes to the log sequentially, one file at a time. Additional log files don’t improve performance and are only added in specific situations, such as when the drive holding the existing log runs out of space.
Data files are organized into filegroups, which are logical containers. When you create a table or index, you can specify the filegroup it belongs to, and SQL Server allocates its pages across the files in that filegroup. A filegroup doesn’t determine where anything sits on disk; each file’s physical location is set by its own path. Log files don’t belong to any filegroup.
SQL Server supports three types of filegroups:
- Rows filegroups contain ordinary data files. The PRIMARY filegroup is one, and it’s where tables, indexes, and in-database BLOBs live by default.
- FILESTREAM filegroups contain file system directories, which Microsoft calls data containers, rather than data files. These directories are the interface between the Database Engine and the file system, and they’re the foundation for FILESTREAM and FileTable, covered below.
- Memory-optimized data filegroups hold the checkpoint files for In-Memory OLTP tables. They’re built on the same FILESTREAM mechanism behind the scenes, and a database can have only one.
Why large objects strain the default layout
Unless you choose otherwise, a large object is stored in a rows filegroup like everything else. A 40MB PDF stored as a varbinary(max) value sits in the same data files as customer records and indexes. It’s read through the same pages, cached in the same buffer pool, and copied in every full backup.
Keeping files in the database provides transactional consistency, unified security, and one backup that covers everything, but can increase database size, lengthen backup and restore times, and consume buffer pool memory that could otherwise serve queries.
FILESTREAM vs. FileTable vs. BLOB: SQL Server’s file storage options
SQL Server offers three ways to keep files under database control. They are not three alternatives on a menu. FileTable is built on top of FILESTREAM, not beside it, which is a common source of confusion.
In-database BLOBs are the simplest option and the one you get by default. A varbinary(max) column stores the bytes inside the database, is fully transactional, and requires no configuration to enable. Its ceiling is 2GB per value. SQL Server backups can include BLOB, FILESTREAM, and FileTable data; FILESTREAM and FileTable data are handled through their FILESTREAM filegroups. Below Microsoft’s 1MB guideline, this is often a practical choice; its simplicity may outweigh the potential efficiency gains of other options.
FILESTREAM integrates the Database Engine with an NTFS or ReFS file system by storing varbinary(max) data as files on disk while SQL Server continues to manage it. The 2GB ceiling disappears because FILESTREAM BLOB sizes are limited only by the volume they sit on. It also changes the memory picture: FILESTREAM caches file data in the NT system cache rather than the SQL Server buffer pool, so that buffer memory stays available for query processing. For a server under memory pressure, it’s often the most consequential difference of the three file storage options.
Two constraints are worth knowing before you commit. FILESTREAM applies to SQL Server on Windows, so it is not an option on Linux deployments. And encryption is not supported on FILESTREAM data, which can surprise teams who assume database-level encryption covers everything the database touches. If that data is sensitive, plan protection at another layer and confirm the current behavior for your release.
FileTable adds a filesystem-like directory structure on top of FILESTREAM. Every row represents a file or a directory, and the contents are exposed through a Windows share, so existing Windows applications can read and write files without code changes. Those Windows API operations are non-transactional. FileTable is a distinct table type with a predefined schema, not an add-on to an existing table. It is built on FILESTREAM and exposes files through a Windows share, while T-SQL access remains transactional. If you need an application to keep browsing a file share while SQL Server manages the files behind it, FileTable is designed for that.
When to store files outside the database
A common alternative to in-database storage is to keep files outside SQL Server entirely. Each file is written to a file storage system or an object store, and the database stores only its path or object key in a column. Microsoft notes that this is a typical pattern for enterprises: Unstructured file data is kept in the file system, while the related metadata is stored in a relational database.
This approach keeps the database smaller, so database backups and restores scale with the relational data rather than with total file volume. It also lets files be managed, tiered, and scaled independently of the database. For large volumes of unstructured data, it’s often the more cost-effective and scalable option.
Because SQL Server no longer manages the files, some responsibilities move from the database engine to the application and operations teams.
Challenges to address
- Consistency between files and records. The database can’t guarantee that a file and its row stay in sync. A failed upload can leave a row pointing to a missing file, and a deleted row can leave an orphaned file behind.
- Transactions. A database transaction can’t include the file write. If one operation succeeds and the other fails, the application has to detect and resolve the mismatch.
- Backup and restore coordination. Database backups and file store backups run separately. Restoring both to the same point in time takes planning, especially for point-in-time recovery.
- Security. SQL Server permissions don’t extend to external files. Access controls on the file system or object store must be configured and maintained separately.
- High availability and disaster recovery. SQL Server features such as Always On availability groups replicate the database but not external files. The file store needs its own replication or failover strategy.
- Deletion and retention. Removing a record doesn’t remove its file. Retention policies and deletions have to be applied in both places.
Common mitigations
Teams typically address these challenges with a few standard practices:
- Write the file before inserting the row, so a failure leaves an orphaned file rather than a broken reference.
- Run a periodic reconciliation job that compares stored paths against the files that actually exist.
- Handle deletion in two phases, with a cleanup process that removes files after their records are deleted.
These practices are well established, but they add application code and operational processes that need to be built, tested, and maintained. That ongoing effort is a key part of the comparison with FILESTREAM, alongside performance and cost.
How underlying storage affects performance
These options change how SQL Server handles file I/O, but they do not eliminate the need for capable storage. FILESTREAM routes large-object I/O through the file system rather than the database engine, so performance depends on whether the underlying volume can sustain the workload’s throughput and latency requirements. If it cannot, the storage layer remains the bottleneck.
These considerations also apply to remote FILESTREAM access. Remote access uses SMB, and FILESTREAM data is not cached on the client, so writes are sent to the server. Microsoft recommends that remote applications combine small writes into larger ones to improve efficiency. Applications using remote FILESTREAM access should evaluate network throughput, storage performance, and write patterns together.
Regardless of which option you pick, the underlying storage affects the latency and throughput available to the workload. Block storage on NVMe flash can provide different performance under mixed random and sequential workloads than a general-purpose volume provisioned for a file share. Large-object workloads may expose those differences quickly.
How Everpure helps
Everpure™ FlashArray™ gives SQL Server file and large object workloads a storage layer that holds up under the mixed I/O these features generate. Its NVMe architecture delivers consistent low latency whether the workload is transactional queries against relational tables or streaming reads of multi-gigabyte objects out of a FILESTREAM container, which matters because those two patterns usually share a system.
For Microsoft SQL Server environments, Everpure enables fast recovery of 10TB SQL Server databases in 10 minutes or less, and proven 99.9999% availability for FlashArray with zero planned downtime. Restore duration is the constraint that in-database file storage makes worse, so it’s the number worth checking against your own recovery objectives before deciding how much file data to keep inside the database.
Everpure snapshots make database copies fast and space-efficient, which changes what is practical when a database carries significant file volume. Refreshing a test environment stops being a capacity decision. Evergreen//One™ aligns that capacity with consumption rather than a purchase cycle, delivering up to 35% lower total cost of ownership, which suits file volumes that grow steadily and rarely shrink.
Explore Everpure database storage solutions to see how the storage layer under SQL Server can change what your file storage architecture can do.
Conclusion
The right SQL Server file storage option depends mainly on file size, access pattern, and how closely files need to stay consistent with their database records.
- In-database BLOBs (varbinary(max)) are typically a good fit for smaller files and modest volumes. Microsoft’s guidance suggests objects under about 1MB often perform better stored this way.
- FILESTREAM suits larger files that need to stay transactionally consistent with their rows. It also keeps file data out of the SQL Server buffer pool.
- FileTable is designed for Windows applications that need to access files through a standard file share.
- External file or object storage is often the most scalable and cost-effective choice for large volumes of unstructured data. It does require the application to manage consistency, security, and cleanup.
Whichever option you choose, file I/O still depends on the underlying storage. Each option changes where large-object I/O happens rather than removing it. The storage layer’s latency and throughput affect how well the chosen approach performs, so it should be evaluated alongside the file storage design.
EXCERPT:
Managing large objects in SQL Server requires choosing the right storage strategy. Learn how BLOBs, FILESTREAM, and FileTable differ and how the storage layer underneath plays a role in performance.
SUMMARY:
SQL Server file storage options—including BLOBs, FILESTREAM, FileTable, and external storage—offer different trade-offs in transactional consistency, application access, backup and recovery, scalability, and performance.
SQL Server file storage FAQs
Should you store files in a SQL Server database?
Usually not. For most applications, storing the file externally and keeping a path in the database is simpler and cheaper and scales further. Store files in the database when the file and its row must stay transactionally consistent and you do not want to build and maintain that reconciliation yourself. Microsoft recommends using FILESTREAM for objects averaging more than 1MB when fast read access is important; smaller objects may be better suited to in-database varbinary(max) storage.
What is FILESTREAM in SQL Server?
FILESTREAM is a SQL Server feature that stores varbinary(max) data as files on an NTFS or ReFS file system while the Database Engine continues to manage it. Transact-SQL can insert, update, query, and back up that data, and Win32 interfaces can stream it. It removes the 2GB per-value limit and keeps file caching out of the SQL Server buffer pool. It applies to SQL Server on Windows.
What is the difference between FILESTREAM and FileTable?
FileTable is built on top of FILESTREAM rather than being an alternative to it. FILESTREAM gives you file-system storage for a column, accessed through T-SQL or Win32 APIs. FileTable adds a directory hierarchy and exposes it as a Windows share, so ordinary Windows applications can work with the files unchanged. Every row in a FileTable represents a file or a directory.
How do you store images and documents in SQL Server?
There are several options. A varbinary(max) column stores them inside the database, which suits small files and low volumes. FILESTREAM stores them on the file system under database control, which suits larger objects. FileTable does the same but presents a browsable share for Windows applications. For high volumes of large files, storing them outside the database with a path in a column is often the better architecture.
Can you encrypt FILESTREAM data?
Encryption is not supported on FILESTREAM data, so database-level encryption does not extend to files stored this way. If the file content is sensitive, plan for protection elsewhere, at the storage or application layer, and confirm the behavior for the specific SQL Server release you’re running before you design around it.
