--- title: NVARCHAR(MAX) is not a safe default date: 2026-10-15 tags: sql, sql-server, database, data-types, ef-core summary: Why NVARCHAR(MAX) as a lazy default costs memory, opens the door to junk data, and blocks indexes. And when MAX is the right choice. --- `NVARCHAR(MAX)` often ends up in a schema because nobody chose a length. EF Core does it for you: every `string` property becomes `nvarchar(max)` unless you configure a length. It works, until it doesn't. ## Memory grants Before a query runs, SQL Server reserves memory for its sorts and hashes (`ORDER BY`, `GROUP BY`, `DISTINCT`, hash joins). How much it reserves depends on the estimated row size. SQL Server doesn't know how long your strings really are, so it guesses from the declared size: ```ascii caption="estimated size per value" NVARCHAR(100) declared 200 bytes -> guess: half of 200 ~100 bytes NVARCHAR(MAX) declared 2 GB -> guess: half of 8000 in-row ~4000 bytes NVARCHAR(100) |#| NVARCHAR(MAX) |########################################| ``` The real data doesn't matter. A `Name` column holding 20 characters is estimated at about 4000 bytes per row as `MAX`. Do that with several columns, multiply by the row count, and the query asks for far more memory than it needs. ```sql title="same data, different grant" -- Same rows in both tables, only the declared type differs -- Name nvarchar(max) SELECT TOP (1000) Id, Name FROM dbo.Customer_Max ORDER BY Name; -- Name nvarchar(100) SELECT TOP (1000) Id, Name FROM dbo.Customer_100 ORDER BY Name; ``` Compare *Memory Grant* on the `SELECT` operator in the actual execution plan. The `MAX` query asks for much more. Why it hurts: - Memory granted to one query isn't available to others or to the buffer pool. - When memory is short, queries wait for their grant before they can start (`RESOURCE_SEMAPHORE` wait). - Only columns that flow through a sort or hash count. A plain lookup by key doesn't need a grant. ## No limit means no limit `NVARCHAR(MAX)` holds up to 2 GB per value. If an API or form writes into that column and nothing checks the length, any client can fill it: a broken script, a bot hammering an unauthenticated endpoint, a loop concatenating the same text forever. ```http title="Castle Aaargh, 500 MB later" POST /api/feedback { "comment": "Here may be found the last words of Joseph of Arimathea. He who is valiant and pure of spirit may find the Holy Grail in the Castle of Aaaaaaaaaarrrrrrrrrrrrrrggggggggg... ... 500 MB later ... ...gggggggggghhhhhhhhhhhhhh" } ``` > "If he was dying, he wouldn't bother to carve *Aaaaarrrrrrggghhh*. He'd just say it!" (Monty Python and the Holy Grail). Your API doesn't argue. It stores every last `r`, `g` and `h`. Validate in the application, but let the column be the last line of defence. `NVARCHAR(500)` rejects the insert with a truncation error instead of storing 500 MB. ## Anonymization scripts Copying production data to a test system usually means scrambling personal data first. A badly written script fills each value up to the column size instead of scrambling what is there. With `MAX` that size has no cap, so a bad script can cost a lot more: ```ascii caption="scrambling a name, example" before Name = "Peter Schnidrig" scrambled Name = "Customer 48213" badly written Name = "Kxqrmwbtlouzaqpfhezmvc... ...thousands of chars later...rtq" ``` ```sql title="a badly written script" -- fills every row to the column size -- @columnSize: whatever the tool uses as the column size UPDATE dbo.Customer SET Name = REPLICATE(CAST(N'x' AS nvarchar(max)), @columnSize); ``` With `NVARCHAR(50)` no value can exceed 50 characters: the script stays small or fails with a truncation error. With `MAX` there is no cap, and every row of a column that held 50-character names can grow to thousands of characters. The test database and its log grow with it. ## Other bad parts - **No index key.** A `MAX` column can't be an index key column, so no seeks on it. It can only be an `INCLUDE` column. - **Off-row storage.** Values over 8000 bytes move to separate LOB pages. Reading them costs extra page reads, and row or page compression doesn't compress those pages. - **No documentation.** `NVARCHAR(20)` for a postal code tells the next developer what to expect. `MAX` tells them nothing, and the frontend has no length to validate against. ## Fine for - Text that really has no sensible upper limit: article bodies, Markdown or HTML, JSON documents, notes, log messages. - Anything that can exceed 4000 characters. That is the largest `NVARCHAR(n)`, so above it `MAX` is the only choice. ## Single rows vs. lists Fine: open one blog post and show its body. The `MAX` column is fetched for a single row, found by `Id`. There is no sort or hash, so no memory grant, and the `WHERE` on `Id` uses the primary key's index. ```sql title="fine" SELECT Title, Body FROM dbo.Post WHERE Id = @id; ``` Not fine: the `MAX` column takes part in a query over many rows. ```sql title="not fine" -- sorts on it: memory grant sized for ~4000 bytes per row SELECT Id, Title FROM dbo.Post ORDER BY Body; -- groups on it: same SELECT Body, COUNT(*) FROM dbo.Post GROUP BY Body; -- filters on it: Body can't be an index key, so even a prefix search scans every row SELECT Id FROM dbo.Post WHERE Body LIKE N'grail%'; -- only selects it, but the sort carries it along: the grant counts it too SELECT TOP (50) Id, Title, Body FROM dbo.Post ORDER BY CreatedAt DESC; ``` The last one is the sneaky one. You don't sort *on* `Body`, but every sorted row carries it, so it counts toward the memory grant. List views should select the short columns and load the body when a single item is opened. ## Fix it in EF Core Set a length per property: ```csharp title="per property" modelBuilder.Entity() .Property(c => c.Name) .HasMaxLength(100); // or with an attribute [MaxLength(100)] public string Name { get; set; } = ""; ``` Or change the default for every string, and opt in to `MAX` where you really need it: ```csharp title="default for all strings" protected override void ConfigureConventions(ModelConfigurationBuilder builder) { builder.Properties().HaveMaxLength(256); } // the exception modelBuilder.Entity() .Property(p => p.Body) .HasColumnType("nvarchar(max)"); ``` ## Find them in an existing database ```sql title="all MAX string columns" SELECT s.name AS [schema], t.name AS [table], c.name AS [column], ty.name AS [type] FROM sys.columns c JOIN sys.tables t ON t.object_id = c.object_id JOIN sys.schemas s ON s.schema_id = t.schema_id JOIN sys.types ty ON ty.user_type_id = c.user_type_id WHERE c.max_length = -1 AND ty.name IN ('nvarchar', 'varchar') ORDER BY s.name, t.name, c.name; ``` Then check what is really stored before you pick a length: ```sql title="longest value" SELECT MAX(LEN(Name)) AS LongestName FROM dbo.Customer; ``` ## TL;DR - SQL Server estimates every `MAX` value at about 4000 bytes, which inflates memory grants for sorts and hashes. - No length limit lets any client store up to 2 GB per value. - `MAX` columns can't be index keys, and long values live off-row. - Use `MAX` for real free text over 4000 characters. Give everything else a length. > Pick a length on purpose. `MAX` should be a decision, not a default.