Do you compress large JSON documents and keep them off busy temporal tables?

SQL Server temporal tables copy the whole row into history on every update. A large JSON document sitting on a busy row (mission briefing, workflow payload, form answers — often tens of KB of nvarchar(max)) gets rewritten into history even when the JSON did not change. That can grow history by tens of GB a month and fill the data file, while the live table itself still looks fine.

Treat large JSON as a document, not as another column on a hot temporal row. Compress it, and when other fields on the parent change often, move it to its own 1:1 table. See Do you use temporal tables to audit data changes? and Do you keep database tables small?.

Why this matters

• History multiplies every byte - An unchanged 40 KB JSON on a row updated 10 times a day becomes ~12 MB of history per month for that one row alone
• Writes get slower - Larger rows mean more page splits, more log, and more I/O on every update (see Do you avoid large database writes?)
• Compression is cheap - Gzip of whole JSON documents commonly shrinks them to about a twelfth of the original size, and the app can share one converter everywhere

1. Store JSON compressed as gzip in varbinary(max)

Keep the document as a string in the domain model. Persist it as varbinary(max) through a shared EF Core ValueConverter that gzip-compresses on the way in and decompresses on the way out.

Encode the text as UTF-16LE before gzip so the bytes interoperate with SQL Server COMPRESS() / DECOMPRESS() on nvarchar. The app and T-SQL can each read what the other wrote.

public class HeroMission
{
    public Guid Id { get; private set; }
    public Guid TeamId { get; private set; }
    public MissionStatus Status { get; private set; }

    // Tens of KB of briefing JSON rewritten into history on every Status change
    public string BriefingJson { get; private set; } = string.Empty;
}

❌ Figure: Bad example - Plain nvarchar(max) briefing on a temporal HeroMission row is copied into history whenever Status changes

public sealed class CompressedJsonConverter : ValueConverter<string, byte[]>
{
    public CompressedJsonConverter()
        : base(
            plain => Compress(plain),
            bytes => Decompress(bytes))
    {
    }

    private static byte[] Compress(string value)
    {
        // UTF-16LE matches SQL Server COMPRESS() on nvarchar
        var utf16 = Encoding.Unicode.GetBytes(value);
        using var output = new MemoryStream();
        using (var gzip = new GZipStream(output, CompressionLevel.Optimal))
        {
            gzip.Write(utf16);
        }
        return output.ToArray();
    }

    private static string Decompress(byte[] value)
    {
        using var input = new MemoryStream(value);
        using var gzip = new GZipStream(input, CompressionMode.Decompress);
        using var reader = new StreamReader(gzip, Encoding.Unicode);
        return reader.ReadToEnd();
    }
}

// In OnModelCreating:
builder.Entity<MissionBriefingDocument>()
    .Property(x => x.BriefingJson)
    .HasConversion<CompressedJsonConverter>()
    .HasColumnType("varbinary(max)");

✅ Figure: Good example - A shared ValueConverter stores the document as gzip varbinary(max) and stays compatible with SQL COMPRESS/DECOMPRESS

Read compressed values in T-SQL with:

SELECT CAST(DECOMPRESS(BriefingJson) AS nvarchar(max)) AS BriefingJson
FROM dbo.MissionBriefingDocument;

✅ Figure: Good example - T-SQL decompresses the same bytes the EF converter wrote

Keep plain nvarchar only when SQL must query the JSON itself — EF primitive collections / OPENJSON, CDC columns, ISJSON constraints, or framework payloads such as outbox, Hangfire, or Identity grants.

2. Move large JSON to its own 1:1 table when the parent is busy

Compression alone is not enough if other columns on the parent change constantly. Every parent update still writes a new history row that includes the (now smaller) blob. Split the document out.

// Temporal parent - updated often as the mission runs
public class HeroMission
{
    public Guid Id { get; private set; }
    public Guid TeamId { get; private set; }
    public MissionStatus Status { get; private set; }
    public MissionBriefingDocument? Briefing { get; private set; }
}

// Temporal 1:1 - history only when the briefing document changes
public class MissionBriefingDocument
{
    public Guid HeroMissionId { get; private set; }
    public string BriefingJson { get; private set; } = string.Empty;
}

✅ Figure: Good example - Authored briefing lives on its own temporal 1:1 table, so Status changes on HeroMission do not rewrite the document into history

// Non-temporal 1:1 - rewritten on every progress tick, no history needed
public class MissionRuntimeState
{
    public Guid HeroMissionId { get; private set; }
    public string ProgressJson { get; private set; } = string.Empty;
}

✅ Figure: Good example - Transient runtime JSON stays off temporal history entirely

3. Reclaim space without deleting authored history

After you migrate:

• Do not drop history of authored content — that history is the audit record
• Compressible copies that can be rebuilt (for example a denormalised cache of the same document) may be dropped once the new column or table is verified
• Shrinking files is a separate ops step; stopping the growth comes from the schema change above

Migrate with expand / contract

Do not flip the column type in place on a large table. Use an expand/contract migration (see Do you know to use code migrations?):

1. Expand - Add the new varbinary(max) column (or 1:1 table)
2. Dual-write - App writes old and new forms
3. Backfill - Batch-update existing rows (SQL COMPRESS or the same converter)
4. Preserve history - Backfill only touches current rows, and dropping a column from a temporal table also drops it from history. Copy historical document versions with their original ValidFrom / ValidTo periods into the new history table (set SYSTEM_VERSIONING = OFF, insert the old versions, then set it back ON with HISTORY_TABLE pointing at that table). Verify row counts and spot-check samples against the old history
5. Contract - Switch current and historical reads to the new form, then drop the old column

-- Backfill in small batches using SQL Server COMPRESS (UTF-16LE for nvarchar)
WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) dbo.HeroMission
    SET BriefingJsonCompressed = COMPRESS(BriefingJson)
    WHERE BriefingJsonCompressed IS NULL
      AND BriefingJson IS NOT NULL;

    IF @@ROWCOUNT = 0 BREAK;
END

✅ Figure: Good example - SQL COMPRESS backfills the new column in bounded batches with bytes the EF converter can decompress

Run each batch as its own commit, not inside one migration transaction, and ideally off-peak. On a temporal table every batch also adds history rows, so watch log and history growth while it runs.

Rejected options

When not to use this

• Small JSON (a few hundred bytes) that is queried with OPENJSON or EF primitive collections — keep plain nvarchar and index what you need
• Framework-owned payloads (outbox, Hangfire, Identity) — leave those as the framework expects
• Documents that must be searched inside SQL — compress only after you have extracted the queryable fields into real columns