I'm asking this out of curiosity, being inspired by this question.
We know that VARCHAR(MAX)
values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).
We can add a VARCHAR(MAX)
column, as INCLUDE
d, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?
Best Answer
Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:
Now insert some rows with values that take 8000 bytes for the
VARCHAR(MAX)
column and check out the DMF:There are no LOB pages in the index:
But if I add rows with values that take 8001 bytes:
Now I have 1 LOB page in the index for every row that I just inserted:
You can also see this with
SET STATISTICS IO ON;
and the right query. Consider the following query that only looks at rows with 8000 bytes:Results upon executing:
If I instead query the rows with 8001 bytes:
Now I see lob reads: