Keeping Large Blobs Out of Your Main Tables

A document list should not read every file’s bytes. Keeping large blobs out of your main tables protects the common path. A list of documents needs names, dates, and owners. It rarely needs every byte of every file. Separate the hot metadata path from payload access and measure the effect.

Understand Row and Off-Row Storage

SQL Server can store large-value columns in-row when they fit, or use off-row LOB storage with an in-row pointer. Table options can influence that behavior. Even when bytes live off-row, selecting the column asks SQL Server to fetch and transmit them. The primary performance rule is to avoid reading payloads when a request needs only metadata.

I check actual row size and query patterns before changing table layout. A small thumbnail occasionally included in a list is different from a multi-megabyte original file. Large-value data has storage and logging costs that the common read path should not pay unnecessarily. Does the list page ever need to fetch the payload bytes?

Avoid SELECT Star on Tables With Large Blobs

SELECT * is convenient during exploration but risky in application code against a table with large blobs. A schema change can add a payload column to every result without changing the caller’s source. That increases network transfer, client memory, and potentially LOB reads. Explicitly select the columns the screen uses.

This query shows a metadata-only list. The second query retrieves bytes only for the chosen document. The split is simple and makes payload access visible in code review.

SELECT DocumentID, FileName, UploadedAt, ContentType
FROM dbo.Documents
WHERE OwnerID = 42
ORDER BY UploadedAt DESC;

SELECT ContentBytes
FROM dbo.Documents
WHERE DocumentID = 1001;

Use a Separate Payload Table for Large Blobs

A one-to-one payload table can keep the frequently accessed entity row narrow. The metadata table stores identity, ownership, type, length, and checksum, while the payload table stores varbinary(max). A foreign key preserves the relationship. This separation makes it harder for ordinary queries to fetch bytes accidentally.

It adds a join for operations that need both pieces and can complicate inserts and deletes. Keep those operations in one transaction when consistency matters. I test the common list and download paths before deciding that the extra table is worthwhile. The point is selective access, not schema purity.

Build a Clear Relationship

The example uses the same DocumentID as a primary key in the payload table and a foreign key to the metadata row. It is a design illustration. Adapt names, compression, filegroups, and security to the application. The foreign key does not automatically create every useful access path in unrelated designs, but the primary key here supplies direct payload lookup.

A deletion workflow should remove or retain bytes according to policy. Keep transaction scope bounded when removing very large values.

CREATE TABLE dbo.DocumentPayload
(
    DocumentID bigint NOT NULL
        CONSTRAINT PK_DocumentPayload PRIMARY KEY,
    ContentBytes varbinary(max) NOT NULL,
    CONSTRAINT FK_DocumentPayload_Documents
        FOREIGN KEY (DocumentID)
        REFERENCES dbo.Documents(DocumentID)
);

SELECT p.ContentBytes
FROM dbo.DocumentPayload AS p
WHERE p.DocumentID = 1001;

Consider the Table Option

The large value types out of row table option can force supported large-value types out of the base row, leaving a pointer. The default can keep smaller values in-row when they fit. Changing the option affects storage behavior and can require data updates or migration work to realize the intended layout. It is not a universal speed switch.

Measure the actual common query and LOB access before changing it. A separate payload table provides a clearer application boundary when the main concern is accidental selection. I prefer the design that developers can recognize and test, not an obscure option that hides the same broad SELECT * calls.

Measure Space and I/O

Use sys.dm_db_partition_stats and allocation views to examine in-row, row-overflow, and LOB allocation where needed. Compare logical reads and bytes returned for metadata queries before and after the split. Backup size and restore time can remain large if the payload still lives in the same database. Table separation improves access paths, not magically total storage.

If payloads are rarely read, consider FILESTREAM or external storage only after evaluating consistency, security, and recovery requirements. Moving bytes out of the main table is one decision. Moving them out of the database is another. Keep those choices separate so the operational consequences are clear.

Keep Payload Metadata Useful

Store content type, size, checksum, upload state, and retention information with the metadata. That lets the application display useful details and validate a download without reading the full blob. A checksum can reveal corruption or mismatched external files, but generating and verifying it has cost. Decide when integrity checks run.

I include a clear status for uploads that are not yet complete, especially when bytes are stored externally. An incomplete file should not look like a successful document to the user. For database-managed payloads, one transaction can keep metadata and bytes together where the workload allows.

Plan Backups and Retention for Large Blobs

Large payload tables affect backup, restore, replication, and availability group movement. A separate filegroup can support specific backup strategies in supported configurations, but it increases operational complexity. Test a restore with both metadata and payload present. A fast list query is not enough if recovery takes longer than the business can accept.

Retention can remove old large blobs while retaining summary metadata, but that is a business and legal decision. Use bounded deletes and monitor log volume. I document which bytes are retained, archived, or removed and how the application behaves after each state change.

Test the Two User Paths

Benchmark a metadata list with realistic result size and a payload download with realistic file sizes. Measure SQL reads, network bytes, response time, and memory use in the application. Confirm that list queries never pull ContentBytes accidentally. Then test upload, delete, backup, and restore.

Keeping large blobs Out of Your Main Tables is about protecting the common path. The payload still needs a secure, recoverable home. A narrow list row helps users find the document. The bytes should travel only when someone actually opens it.

A related table also helps permissions. The metadata list can be available to a broad application role while the payload query uses a narrower path with audit logging. That separation is useful for sensitive documents. Test that authorization is enforced at download time, because hiding the blob column from a list screen is not access control.

Related reading on this blog: How to Limit Output of Varchar(max), Nvarchar(max) in SELECT Statement? Interview Question of the Week #218 and Retrieve All the Data from VARCHAR(MAX) Column.

A large blob is not an ordinary list column, it is a payload to fetch only when needed.

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