Are "out of the row" fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored.

I have the following questions:

• Is each field stored out of the row or only the max ones?

• If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the ...

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row...

Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation:

The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used sp_tableoption to change the default, your MAX data will most likely be stored in-row.

That said, it is poor practice to use MAX data types for values that will never exceed 8000 bytes - use a non-MAX type instead. Aside from anything else, performance is often significantly poorer when dealing with MAX types, because SQL Server must be prepared to cope with data that might be up to 2GB in size.


Is each field stored out of the row or only the max ones?

Only the MAX ones. In addition, if a previously in-row MAX column is moved off-row, only that column in that row is affected. It is replaced in-row by a pointer to the off-row LOB structure. There are also circumstances where non-MAX columns may be moved off-row.


If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

Scanning the clustered index traverses only in-row data. If off-row data is needed for the query, it is looked up using the in-row pointer.

StackExchange Database Administrators Q#87435, answer score: 13