Storing Images in SQL Server: varbinary(max) or FILESTREAM

Storing images in SQL Server takes ten minutes to build and months to regret if you skip the planning. The code is easy. The surprises are backup size, memory pressure and a list page that suddenly crawls. Let me show where the bytes go, so you can pick between varbinary(max) and FILESTREAM with open eyes.

Start with varbinary(max)

A varbinary(max) column holds the raw bytes of a picture. No file path, no extra service. I will make one table and add two values. The first is a real 70 byte PNG, written as a hex literal so you need no file. The second is a 5 MB stand-in for a large photo.

DROP TABLE IF EXISTS dbo.ImageDemo;
CREATE TABLE dbo.ImageDemo (
    ImageId int IDENTITY(1,1) PRIMARY KEY,
    FileName varchar(60) NOT NULL,
    ImageData varbinary(max) NOT NULL);
DECLARE @badge varbinary(max) =
    0x89504E470D0A1A0A0000000D49484452000000010000000108060000001F15C4890000000D4944415478DA63FCCFC0500F000485018084A98C210000000049454E44AE426082;
DECLARE @banner varbinary(max) = CAST(REPLICATE(CAST('A' AS varchar(max)), 5000000) AS varbinary(max));
INSERT dbo.ImageDemo (FileName, ImageData) VALUES ('badge.png', @badge), ('banner.dat', @banner);
SELECT FileName, DATALENGTH(ImageData) AS Bytes,
       CONVERT(varchar(8), SUBSTRING(ImageData, 1, 4), 2) AS FirstFourBytes,
       CASE WHEN FileName = 'badge.png' AND HASHBYTES('SHA2_256', ImageData) = HASHBYTES('SHA2_256', @badge)
            THEN 'identical' ELSE '' END AS RoundTrip
FROM dbo.ImageDemo ORDER BY ImageId;

The first four bytes of the PNG come back as 89504E47, the standard PNG signature. The SHA-256 hash of the stored value also matches the hash of what I inserted, so the round trip is lossless. That answers the question readers ask me most: how do I get my picture back out? The query returns bytes, and your application writes them to a file.

The silent truncation trap

The most common bug I see is not a crash. It is half a picture. Shorten the type and SQL Server shortens the bytes without a word.

DECLARE @big varbinary(max) = CAST(REPLICATE(CAST('A' AS varchar(max)), 5000000) AS varbinary(max));
SELECT DATALENGTH(CAST(@big AS varbinary)) AS PlainVarbinary,
       DATALENGTH(CAST(@big AS varbinary(8000))) AS Varbinary8000,
       DATALENGTH(CAST(@big AS varbinary(max))) AS VarbinaryMax,
       DATALENGTH(REPLICATE('A', 5000000)) AS ReplicateNoMax;

Casting a 5,000,000 byte value to a bare varbinary keeps 30 bytes. Casting to varbinary(8000) keeps 8,000. Only varbinary(max) keeps all 5,000,000. REPLICATE has the same ceiling: without a max string it stops at 8,000 characters. When a picture arrives broken, check every variable, parameter and cast on its path.

Where the bytes actually live

Small values sit in the row. Large ones move to separate LOB pages. This query counts the pages for each kind.

SELECT au.type_desc AS StorageType, SUM(au.used_pages) AS UsedPages
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON p.partition_id = au.container_id
WHERE p.object_id = OBJECT_ID('dbo.ImageDemo')
GROUP BY au.type_desc
ORDER BY au.type_desc;

The tiny PNG and the row itself use 2 in-row pages. The 5 MB value uses 625 LOB_DATA pages. That split is why a list page can crawl, so I measured it with logical reads.

SET STATISTICS IO ON;
SELECT ImageId, FileName FROM dbo.ImageDemo ORDER BY ImageId;
SELECT ImageId, HASHBYTES('SHA2_256', ImageData) AS Fingerprint FROM dbo.ImageDemo ORDER BY ImageId;
SET STATISTICS IO OFF;

The first query returns only ids and names, and it reports 0 lob logical reads. The second touches ImageData (I hash it instead of printing 5 MB) and reports thousands of them. A SELECT * touches that column too. Keep the image column out of list queries, and park images in their own table, joined by id.

Loading a file from disk

For real pictures, OPENROWSET with BULK reads a whole file as one value. Here I point it at a folder that does not exist, on purpose.

INSERT dbo.ImageDemo (FileName, ImageData)
SELECT 'logo.png', BulkColumn
FROM OPENROWSET(BULK N'C:\NoSuchFolder\logo.png', SINGLE_BLOB) AS f;

It fails with Msg 4860: “Cannot bulk load. The file does not exist or you don’t have file access rights.” Two causes hide behind that one message. The path is wrong, or the SQL Server service account cannot read it. Check the path first. Point the same statement at a real file and it inserts the bytes.

When FILESTREAM earns its place

FILESTREAM keeps the bytes as files on the Windows disk, managed by SQL Server. Try to create one on a new database and you hit two walls.

SELECT SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamLevel;
CREATE TABLE dbo.PhotoFs (
    PhotoId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    Photo varbinary(max) FILESTREAM NULL);

FilestreamLevel is 0 on my server, which means the feature is off. The CREATE TABLE also fails with Msg 1969, because the database has no default FILESTREAM filegroup. So setup is a short list.

1. Enable FILESTREAM for the Database Engine service in SQL Server Configuration Manager, then restart the service.
2. Set the access level. This changes a server setting, so try it on a dev box first.

EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

3. Add a FILESTREAM filegroup and a container folder to your database.
4. Create the table with a uniqueidentifier ROWGUIDCOL column and a varbinary(max) FILESTREAM column, like the one above.

Here is my rule of thumb, not a law. Values under about 256 KB belong in varbinary(max). Values over about 1 MB lean toward FILESTREAM, or toward plain files with the path stored in a column. In between, measure with your own pictures. Plain files are the lightest option, but then the database and the folder can drift apart.

DROP TABLE IF EXISTS dbo.PhotoFs;
DROP TABLE IF EXISTS dbo.ImageDemo;

Pick the storage by the size of your biggest picture, then test it with a real one.

Image storage is not a column choice, it is a backup and memory decision.

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