May 10, 2007 at 3:15 pm
Hi Everyone,
Question for the group regarding storage settings of the new large value data types (varchar(max), varbinary(max), nvarchar(max), xml). As I understand it by default anything in these columns smaller than about 8k will stay in the row. Anything larger will be stored in the TEXTIMAGE_ON location. sp_tableoption offers ‘large value types out of row’ to force storage of these columns out of row, with a 16-byte pointer to the root. Most of the recommendations suggest only use this setting when data values will consistently be larger than 8k. I almost view this as a way to vertically partition out the large value data type column. If for example the nvarchar(max) column isn’t always returned in the result set.
I was wondering what the general thought is about using this setting for data values smaller than 8k.
May 11, 2007 at 12:50 am
Rob,
unless the large value type column is consistently returned in the queries I usually recommend to use sp_tableoption ‘large value types out of row’. The reason simply is that queries which doesn't return the LOB column need to scan a lot less pages.
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply