Seeing Exactly Where SQL Server Stores Each Column
You add a couple of varchar(max) columns to a table because you are not sure how big the values will get. Most rows hold a few dozen characters. A handful hold tens of thousands. Everything works, but a question nags at you: where does each of those values physically live? Small ones surely sit right in the row, but the giant ones cannot, because a row has to fit on an 8 KB page.[1] So at some size a column gets evicted from the row and stored somewhere else. Which columns, and at what size?
You can stop guessing. SQL Server will tell you directly, through a pseudo-column almost nobody mentions and a built-in function that has never appeared in the documentation.
The Pseudo-Column Nobody Told You About
You already know varchar(max) can be stored off the row. What is harder to see is exactly when an individual value crosses that line. There is an undocumented pseudo-column, %%ROWDUMP%%, that hands you the raw bytes of a row’s physical record as varbinary(max). It is a cousin of the better-known %%physloc%%, which returns a row’s file/page/slot address. Neither is documented or supported, so neither belongs in production code, but both are wonderful for poking at internals on a scratch database.
On its own a wall of hex is not much use. The second undocumented piece is an intrinsic table-valued function, sys.fn_RowDumpCracker, that takes one of those row dumps and shreds it into a tidy result set, one row per variable-length column, describing where that column actually ended up. Feed the dump in, get a map of the row out.
A Table That Can’t Make Up Its Mind
Here is a table with two varchar(max) columns and four rows that deliberately span the interesting range, from a few bytes up to values far too large to keep in the row:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.RowStorageDemo ( RowID int IDENTITY(1,1) NOT NULL , SomeVarNull varchar(max) NULL , SomeVarNoNull varchar(max) NOT NULL ); DECLARE @a varchar(max) = REPLICATE(CONVERT(varchar(max), 'a'), 9000); DECLARE @b varchar(max) = REPLICATE(CONVERT(varchar(max), 'b'), 19000); INSERT INTO dbo.RowStorageDemo (SomeVarNull, SomeVarNoNull) VALUES ('asdfasdf', 'asdfasdfasdfsadfsdf'); INSERT INTO dbo.RowStorageDemo (SomeVarNull, SomeVarNoNull) VALUES (NULL, 'asdfasdfasdfsadfsdf'); INSERT INTO dbo.RowStorageDemo (SomeVarNull, SomeVarNoNull) VALUES (@a, 'asdfasdfasdfsadfsdf'); INSERT INTO dbo.RowStorageDemo (SomeVarNull, SomeVarNoNull) VALUES (@b, @b); |
That CONVERT(varchar(max), 'a') is not decoration, and it is worth a short detour. REPLICATE returns the data type of its first argument, and if that argument is not already a max type it is truncated at 8,000 bytes.[2] Write the obvious REPLICATE('a', 9000) and you silently get 8,000 bytes, not 9,000, which is exactly large enough to stay in the row and quietly defeat the whole demonstration. Converting the single character to varchar(max) first lets the result actually reach 9,000 and 19,000 bytes.
Asking Where Every Column Lives
Now apply the cracker to each row. %%ROWDUMP%% produces the bytes, CROSS APPLY sys.fn_RowDumpCracker decodes them, and a couple of DATALENGTH calls let you see the real value sizes next to the verdict:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT st.RowID , DATALENGTH(st.SomeVarNull) AS null_bytes , DATALENGTH(st.SomeVarNoNull) AS nonull_bytes , rdc.colName , rdc.IsInrow , rdc.PrefixBytes , rdc.InRowLength FROM dbo.RowStorageDemo AS st CROSS APPLY sys.fn_RowDumpCracker(%%ROWDUMP%%) AS rdc ORDER BY st.RowID, rdc.colName DESC; |
Which gives you:
|
1 2 3 4 5 6 7 8 9 10 |
RowID null_bytes nonull_bytes colName IsInrow PrefixBytes InRowLength ----- ---------- ------------ ------------- ------- ----------- ----------- 1 8 19 SomeVarNull 1 0 8 1 8 19 SomeVarNoNull 1 0 19 2 NULL 19 SomeVarNull 1 0 0 2 NULL 19 SomeVarNoNull 1 0 19 3 9000 19 SomeVarNull 0 0 36 3 9000 19 SomeVarNoNull 1 0 19 4 19000 19000 SomeVarNull 0 0 48 4 19000 19000 SomeVarNoNull 0 0 48 |
The full function returns a few more columns than I selected here. Besides colName, IsInrow, PrefixBytes, and InRowLength, you also get partition_id, IsSparse, IsRecordPrefixCompressed, and IsSymbol, which become interesting once you start playing with sparse columns and data compression. For plain variable-length storage the four above tell the story.
Reading the Results
Walk the rows from top to bottom and the storage engine’s decision making is right there:
- Row 1 is the easy case. Both values are tiny, eight and nineteen bytes, so both report
IsInrow = 1and anInRowLengththat matches their actual size. Everything lives in the row. - Row 2 stores a
NULLin the first column. It is still in-row, but itsInRowLengthis 0: a NULL variable-length column takes no value bytes at all. - Row 3 is where it gets interesting. The 9,000-byte value can no longer fit in the row, so it is pushed off-row:
IsInrow = 0, and what stays behind in the row is a 36-byte pointer to where the data actually lives. The 19-byte column in the same row is untouched and stays in-row. The engine evicts individual columns, not whole rows. - Row 4 has two 19,000-byte values, and neither can stay. Both report
IsInrow = 0, each leaving a 48-byte pointer in the row. The pointer grew from 36 to 48 bytes because a larger value needs to reference more off-row pages, so the in-row footprint of an evicted column is not fixed; it scales with how much data is parked elsewhere.
Two details are easy to miss. First, the cracker only reports the variable-length columns; the fixed-width int identity column never appears, because fixed-width columns always live in the fixed-data portion of the record and are never candidates for eviction. Second, every verdict here is the storage engine’s own bookkeeping, not an inference from value sizes. You could find the same row with %%physloc%% and crack the page open the hard way, but %%ROWDUMP%% answers the “in or out” question without leaving T-SQL.
What This Buys You
This is more than a curiosity. Off-row storage has real costs: reading an off-row column means following that pointer to another page, which is extra I/O the optimizer cannot always hide. When a query that selects a big varchar(max) column is mysteriously slow, knowing whether the value is in-row or off-row tells you whether you are paying that price. It also explains the classic surprise where a table’s row count is modest but its page count is enormous, because the bulky values are sitting on row-overflow or LOB pages the row only points at.[3]
If you want the matching tool for the other half of the question, where a given row physically sits, I took apart %%physloc%% and sys.fn_PhysLocCracker in an earlier post on finding a row’s physical location. And if you enjoy reading raw record bytes, the same appetite drives the pair of posts where I decode the on-page layout of a DATETIME and the newer date and time types one byte at a time.
A word of caution, since it bears repeating: %%ROWDUMP%% and sys.fn_RowDumpCracker are undocumented and unsupported. They can change or vanish between versions, and they have no business in application code or anything that runs against production. Keep them on a scratch database, where they are exactly the right tool for satisfying your curiosity about where the bytes really go.
Have you ever chased a storage mystery down to a column quietly living off-row? I would enjoy hearing about it. Find me on Bluesky or LinkedIn.
References
- Page and Extent Architecture Guide – Microsoft Learn. Page size of 8 KB, the row-overflow mechanism for variable-length columns, and large object (LOB) storage off the row. ↩
- REPLICATE (Transact-SQL) – Microsoft Learn. REPLICATE returns the type of its first argument, and truncates the result at 8,000 bytes when that argument is not a max type. ↩
- char and varchar (Transact-SQL) – Microsoft Learn. varchar(max) values that exceed the in-row limit are stored as large object data on separate pages. ↩