In SQL Server, a non-max variable-length column can hold up to 8000 characters for the single-byte type and 4000 for the double-byte type. A page is 8 kilobytes, which is 8192 bytes.
The correspondence is close enough that it looks deliberate, 8000 bytes either way, sitting just under the page size with room left over.
Is that actually the reason for the limits, and what is the leftover space for?
Yes, that is exactly the reason, and the history makes it clearer.
The engine stores rows in pages, and a fundamental design rule is that a row must fit on a single page: a row cannot span pages. So the maximum size of any single value is bounded by what can fit in a page alongside everything else.
The 8 kilobyte page was introduced at a specific version, and the maximum length for the variable-length character type went from 255 to 8000 at the same time. Before that the page was 2 kilobytes, and a limit of 255 fitted that era's constraints comfortably.
The double-byte type stores two bytes per character, so 4000 characters is the same 8000 bytes. The two limits are one limit expressed in two units.
So the numbers are not arbitrary. They are the page size minus overhead, rounded down to something memorable.
Worth adding what happens when you exceed it, because that is the design consequence.
The max variants store data off-row when it is too large, keeping a pointer in the row and the data in separate pages. That is why they can hold enormous values and why they behave differently, they cannot be indexed in the same way, some operations are more expensive, and there is a level of indirection on every access.
So the practical guidance that falls out of this: use the fixed maximum when your data genuinely fits, because it stays in the row and behaves predictably. Use the max variant when values may be large, and accept the trade.
The common mistake is declaring everything as the max variant "just in case", which imposes the indirection on data that never needed it.
The same reasoning shows up in other databases with different numbers, which is a nice illustration that these limits are engineering consequences rather than arbitrary choices.
PostgreSQL uses 8 kilobyte pages too by default and handles oversized values by compressing and moving them to a separate storage area automatically, which is a different solution to the same constraint.
Once you know that rows live in fixed-size pages, a lot of otherwise inexplicable limits across different systems stop being surprising.
@sqlserver_sibel · last mo.
The leftover, the difference between 8192 and 8000 - is page and row overhead, and it is worth knowing what lives there:
Add those and the usable space for row data is a bit over 8000 bytes, which is where the round number comes from. The limit is set so that a single maximum-length value plus the necessary overhead still fits.
This is also why you can define a table whose columns sum to more than the row limit and get a warning rather than an error: it is legal to define, and it fails only if you actually insert a row that does not fit.
Reply
Report