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?
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.