Row-Overflow and LOB: When a Row Leaves the Page
A data page is 8 KB, and a single row is capped at 8,060 bytes of in-row data.[1] So what happens when you declare two varchar(5000) columns, or store a 20,000-character varchar(max), in a row that plainly cannot hold it? SQL Server does not refuse the row; it moves part of it off the page and leaves a pointer behind. This post creates both situations, watches the extra allocation units appear, and reads the small in-row footprint that is left when the data lives elsewhere.
This continues the byte-level storage series alongside the NULL bitmap, decimal, and where each column lives in a record. If DBCC PAGE is new to you, this older post covers the mechanics. Every number below is from a SQL Server 2019 instance, and the script is included so you can reproduce it.
Three places a row can live
Every table stores its rows in one to three kinds of allocation unit, and the names tell you exactly what each holds:[1]
- IN_ROW_DATA holds the row itself, the normal data page, and every table has one.
- ROW_OVERFLOW_DATA holds variable-length columns (
varchar,nvarchar,varbinary) that were pushed off the page because the row grew past 8,060 bytes. - LOB_DATA holds large object columns (
varchar(max),nvarchar(max),varbinary(max),xml, and the deprecatedtext/image).
A table only has the second or third unit if it actually needs it. Let us make it need them.
Pushing a row past 8060 bytes
Two varchar(5000) columns. Individually each fits; together, filled, they are 10,000 bytes, well over the limit:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE [dbo].[ovf_demo] ( [id] int NOT NULL , [a] varchar(5000) NOT NULL , [b] varchar(5000) NOT NULL ); INSERT INTO [dbo].[ovf_demo] ( [id], [a], [b] ) VALUES ( 1, REPLICATE('A', 5000), REPLICATE('B', 5000) ); |
The insert succeeds. Asking which allocation units the table now owns shows why:
|
1 2 3 4 |
table alloc_type total_pages used_pages -------- ----------------- ----------- ---------- ovf_demo IN_ROW_DATA 9 1 ovf_demo ROW_OVERFLOW_DATA 9 1 |
A ROW_OVERFLOW_DATA allocation unit appeared. When the row exceeded 8,060 bytes, SQL Server moved the widest variable-length column, one of the 5,000-byte strings, out to an overflow page, and left a 24-byte pointer in the row that references it.[1] The other string stayed in row. This movement is dynamic: shorten the row again with an update and SQL Server can pull the column back in-row.
The trade-off is I/O. A query that touches the overflowed column now follows the pointer to a second page, so a scan that would have read one page reads two. It is the price of allowing rows that do not fit.
Large objects live out of row by design
varchar(max) is different. It is a LOB type, and once its value is large it goes to a LOB_DATA unit as a matter of course, not only when the row overflows:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[lob_demo] ( [id] int NOT NULL , [x] varchar(max) NOT NULL ); INSERT INTO [dbo].[lob_demo] ( [id], [x] ) VALUES ( 1, REPLICATE(CONVERT(varchar(max), 'X'), 20000) ); |
The allocation units:
|
1 2 3 4 |
table alloc_type total_pages used_pages -------- ------------ ----------- ---------- lob_demo IN_ROW_DATA 1 1 lob_demo LOB_DATA 3 3 |
The 20,000 X characters are in the LOB_DATA unit. What is left on the data page is tiny. Here is the in-row record:
|
1 2 3 4 5 6 |
Slot 0 Offset 0x60 Length 63 Record Size = 63 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 id = 1 x = [BLOB Inline Root] Slot 0 Column 2 Offset 0xf Length 48 Level = 0 Unused = 51 UpdateSeq = 1 Type = 4 |
A 20,000-byte value, and the record on the data page is 63 bytes. The column reads [BLOB Inline Root]: an in-row structure that points out to the tree of LOB pages holding the actual bytes. For a small LOB the documentation describes a 16-byte pointer to the separate page tree; the inline root you see here is a slightly larger structure carrying the links, but the principle is the same. The bulk of the data is not on this page.
What this means in practice
- Wide rows are allowed but not free. Declaring
varchar(8000)columns that can jointly exceed 8,060 bytes works, but rows that overflow pay an extra page read whenever the off-row column is touched. Keep frequently-read columns narrow enough to stay in row. - Off-row storage is dynamic for overflow, structural for LOB. A
varchar(n)moves off row only when the row is too big, and can move back. Avarchar(max)value is a LOB and lives inLOB_DATAby design once it is large. - SELECT * is worse than it looks on these tables. Every off-row column that the query does not actually need is still an extra page fetch when it is read. Selecting only the columns you need can turn a two-or-three-page read back into one.
- Consider large value types out of row. The
large value types out of rowtable option, and for LOBs the in-row threshold, control how aggressively data goes off page. The default keeps small values in row for speed; force everything off row only when the in-row bloat is hurting scans of the narrow columns.
The 8,060-byte limit is not a wall your data hits; it is the point at which SQL Server quietly starts keeping part of the row somewhere else and remembering where. Once you can see the allocation units and the pointer, a “why is this scan reading so many pages” question often answers itself.
Have you tracked a surprising page count back to a varchar(max) or an overflowed row? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- Page and Extent Architecture Guide – Microsoft Learn. The 8,060-byte row limit, the ROW_OVERFLOW_DATA 24-byte in-row pointer for variable-length columns, and the 16-byte LOB pointer to the text/LOB page tree. ↩