10

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:

  1. Is each field stored out of the row or only the max ones?
  2. 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 ...

Improve this question
3
  • 2
    Where did that last quoted bit come from? It is not correct.
    – Paul White ♦
    Jan 2, 2015 at 14:53
  • @PaulWhite stackoverflow.com/a/148465/1080354
    – gotqn
    Jan 2, 2015 at 15:16
  • 3
    The full text in the original MSDN thread (by Jacob Sebastian) is correct. The Stack Overflow "quote" loses quite a bit of that. The small fraction of that you quoted above omits all the important bits :)
    – Paul White ♦
    Jan 2, 2015 at 15:22

2 Answers 2

Reset to default
13

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:

BOL extract

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.

Improve this answer
4
  • Is this always true - Scanning the clustered index traverses only in-row data.? For example, if you want do display the NVARCHAR(MAX) field values how is it possible to work only with the in-row-data (if the values are stored out of the row)? Or when you are using the clustered index (because there is no covering index) but you are not going to work with the NVARCHAR(MAX) field the SQL Server is clever enough to see that and skip the look up to out-of-row data?
    – gotqn
    Jan 2, 2015 at 15:27
  • Thanks for the answer. So, finally, if you have two columns - int and nvarchar(max) and you are selecting only the int column, the SQL-Server does not waste resource to read the out-of-row data as it knows you are not going to use it?
    – gotqn
    Jan 2, 2015 at 15:35
  • Thanks a lot. That's very nice. Seems, that using the sp_tableoption you are able to put out of the table everything that is not often used in order to reduce the size of the row, when a lot of clustered index seeks/scans are made.
    – gotqn
    Jan 2, 2015 at 15:39
  • 3
    @gotqn Yes. Off-row was the default for the old LOB types text, ntext and image. You could also store the large types in a separate table, of course.
    – Paul White ♦
    Jan 2, 2015 at 15:50
4

This behavior for large objects storage can be controlled by the table setting:

exec sp_tableoption N'MyTable', 'large value types out of row', <'ON' or 'OFF'>

The reference in the SQL Server 2012 documentation is at: http://msdn.microsoft.com/en-us/library/ms173530.aspx

Therefore you can control where the space is used, in-row or stored out of row.

Improve this answer
1
  • Thanks, I really did not know that you are able to control this.
    – gotqn
    Jan 2, 2015 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.