Should I use (max) size for all my nvarchar/varchar columns?

· by · in , ,

No.

Not “it depends.” Not “usually not.” No.

I see this pattern constantly — someone reads that VARCHAR(MAX) can hold up to 2 GB and thinks, “why would I ever bother figuring out the right size? Just make everything MAX and move on.” It feels pragmatic. It’s actually a trap that costs you indexing, memory, storage efficiency, and sometimes correctness.

Here’s why.

Illustration of warehouse shelves with neatly sized boxes labeled 50, 128, 255 on one side, and oversized MAX containers overwhelming shelves on the other side

The Problems with (MAX)

You Can’t Use a (MAX) Column as an Index Key

This is the dealbreaker most people hit first. You cannot create an index — clustered or nonclustered — with a VARCHAR(MAX) or NVARCHAR(MAX) column as a key column. SQL Server will reject it with Msg 1919:

Column ‘YourColumn’ in table ‘YourTable’ is of a type that is invalid for use as a key column in an index.

You can include (MAX) columns in the INCLUDE list of a nonclustered index — which at least makes covering indexes possible — but you can’t build a B-tree key on that column. No key means no seeks on that column. If you decided every string column should be MAX, you’ve made those columns unsearchable by index.

You Can’t INCLUDE a (MAX) Column in an Index (Before SQL Server 2017)

Prior to SQL Server 2017, (MAX) columns couldn’t even appear in the INCLUDE list of a nonclustered index.

Correction: This is a common misconception. VARCHAR(MAX) and NVARCHAR(MAX) columns can be included as nonkey columns in nonclustered indexes — and have been for a long time. What’s prohibited is using them as index key columns. The restriction above was wrong in my original draft. I’m leaving it here, struck through, because I’ve seen this claim repeated often enough that it’s worth explicitly debunking.

Excess Memory Grants

This one is insidious because it doesn’t throw an error — it just silently wastes memory.

When SQL Server builds an execution plan for a query that sorts or hashes string data, it has to estimate how much memory to request. For memory-consuming operators, (MAX) columns often trigger a much larger estimated row width — commonly around 4,000 bytes for VARCHAR(MAX) — while bounded columns estimate much smaller widths. The exact estimation depends on the operator, SQL Server version, and available statistics, but the general pattern is consistent: MAX columns get dramatically larger memory grant estimates than sized columns.

Run a query that sorts 100,000 rows, and the difference in requested memory can be enormous. If the server can’t grant that much memory, your query waits in RESOURCE_SEMAPHORE until memory frees up, or it spills to tempdb.

Off-Row LOB Storage

When a VARCHAR(MAX) value is short enough to fit in-row (roughly under 8,000 bytes), SQL Server stores it in the data row. But once the value exceeds that threshold, it gets pushed to a separate LOB allocation unit — off-row storage that requires additional I/O to read. (This is distinct from row-overflow storage, which applies to regular variable-length columns when the row as a whole exceeds 8,060 bytes. Both result in off-row data, but through different mechanisms.)

Even when small values are stored in-row, (MAX) columns still have engine-level consequences. SQL Server documents extra fixed allocation for non-null (MAX) columns during sort and worktable operations, which counts against the 8,060-byte row limit in those internal operations.

Constraints and Function Limitations

VARCHAR(MAX) columns can’t be used in:

  • Primary key or unique constraints (because those create indexes)
  • CHECK constraints that reference certain functions
  • As parameters for some built-in functions — though this has improved over successive SQL Server versions
  • Computed column definitions in some scenarios
  • Memory-optimized tables (In-Memory OLTP has historically had restrictions on MAX types)

    • Implicit Conversions

    When you compare a VARCHAR(MAX) value to a VARCHAR(n) value, SQL Server may perform an implicit conversion that changes how the index is used. A VARCHAR(MAX) variable can lead to less efficient seek logic or residual predicates versus a correctly-sized variable — it doesn’t automatically force a scan, but it can worsen plan quality in ways that are hard to spot without checking the actual execution plan.

    The Demo

    Let’s see this in practice. The following code creates a test database with two tables — one using VARCHAR(MAX) and one using VARCHAR(42) — loads 1,000 rows into each, and runs comparison queries.

    Setup: Create the Test Database and Tables

    Nothing fancy here — a clean throwaway database for testing. Always use a dedicated test database for this kind of thing so you can drop it without worry.

    Create the Two Tables

    Same structure, same data type, same NOT NULL constraint. The only difference is VARCHAR(MAX) vs VARCHAR(42).

    Load Test Data

    This generates 1,000 rows of random hex strings. CRYPT_GEN_RANDOM(20) produces 20 random bytes, and CONVERT(..., 1) renders them as a hex string with a 0x prefix. The resulting strings are 42 characters long — which is why the sized table uses VARCHAR(42).

    Try to Create an Index on the MAX Column

    This fails with:

    Msg 1919, Level 16, State 1
    Column ‘SomeString’ in table ‘dbo.VarcharMaxTable’ is of a type that is invalid for use as a key column in an index.

    That’s the core problem. You declared the column as MAX, so SQL Server won’t let you index it — regardless of what the actual data looks like. The data is 42 characters long. The column could have been VARCHAR(42). But because you chose MAX, you’ve locked yourself out of indexing.

    Create an Index on the Sized Column

    This succeeds immediately. A properly sized column can be indexed without issue.

    Compare Query Performance

    Run these and check the execution plans. The query against VarcharMaxTable does a clustered index scan — it reads every row in the table. The query against VarcharSizedTable does a nonclustered index seek followed by a key lookup (or just an index seek if the index covers the query). With 1,000 rows the difference is small. With 10 million rows, one query returns in milliseconds and the other takes minutes.

    Variable Type Matters Too

    This is the subtle one. Both queries run against the properly-sized table with a proper index. But look at the execution plans:

  • When @search is declared as VARCHAR(MAX), SQL Server may use less efficient seek logic — the MAX type on the variable can cause an implicit conversion that adds residual predicates or changes the seek pattern, even when the index on the column is perfectly valid.
  • When @search is declared as VARCHAR(42), the types match, the index is used cleanly, and the plan is optimal.

    • The lesson: it’s not enough to size your columns correctly. Your variables, parameters, and application-layer types need to match too.

    Cleanup

    What About Memory Grants?

    The demo above focuses on indexing, but the memory grant problem is just as damaging — and harder to spot because it doesn’t show up as an error.

    When SQL Server compiles a query that requires a memory grant (sorts, hash joins, hash aggregates), it estimates the size of the data it needs to hold in memory. For string columns, that estimate is based on the declared column size, not the actual data.

    Here’s a rough illustration of how the estimates can differ:

    Declaration Typical estimated bytes per row 100K rows 1M rows
    VARCHAR(42) ~42 ~4 MB ~40 MB
    VARCHAR(MAX) ~4,000 ~400 MB ~4 GB

    These numbers are approximate — actual estimates depend on the operator and SQL Server version — but the magnitude of difference is real.If the server is under memory pressure, your query sits in RESOURCE_SEMAPHORE waiting for a grant that’s wildly oversized for the actual data. Meanwhile, other queries are starved of the memory your query is hoarding but not using.

    SQL Server 2022 didn’t fundamentally change this estimation behavior. The memory grant feedback features (batch mode and row mode) can adjust grants after the first execution, but the initial estimate is still based on declared type size. If you’re running ad hoc queries or queries with OPTION (RECOMPILE), you get the inflated estimate every time.

    The Right Approach

    Size your columns to match the data they hold:

  • Email addresses? VARCHAR(254) — that’s the RFC maximum.
  • US phone numbers? VARCHAR(15) — with formatting characters.
  • Two-letter country codes? CHAR(2).
  • A person’s name? Pick a reasonable maximum — NVARCHAR(200) covers virtually every real-world name.

    • If you genuinely need to store unbounded text — user comments, log messages, XML payloads, document bodies — then VARCHAR(MAX) or NVARCHAR(MAX) is the right choice. That’s what it’s for. But use it because the data actually requires it, not because you couldn’t be bothered to think about the maximum length.

    When you’re unsure of the right size, query the existing data:

    Then pick a size with some headroom above the observed maximum. You can always ALTER COLUMN to make it bigger later — that’s a metadata-only change for variable-length types. Making it smaller is harder, so err on the side of generous-but-not-MAX.

    Summary

    VARCHAR(MAX) and NVARCHAR(MAX) exist for columns that genuinely need to hold large, variable-length data. They’re the wrong choice for columns where you know — or can estimate — the maximum length.

    Using MAX everywhere is not a shortcut. It’s a decision to give up index key usage, inflate memory grants, increase the odds of off-row LOB storage, and introduce implicit conversion quirks — all to avoid spending thirty seconds thinking about how long the data actually is.

    Size your columns. Your future self will thank you.

    Tags: , , , ,