I reading BOL, and try to understand when it is better to use text datatype,
and when varchar(max). From what I read, it looks like varchar(max) is close
to text field with turned on 'text in row' option. What if I want to have
only pointer to varchar(max) data in row, is it possible? Or usage of
varchar(max) means what is situations when it may fit to page it always cause
degradation of select performance, degradation of performance of updates in
case if the updates not affect the varchar(max) columns? In which cases usage
of varchar(max) field have performance benefits over usage of text field?

TEXT is in effect deprecated. The BOL says to use VARCHAR(MAX) for new work and to plan to convert existing applications that use TEXT to VARCHAR(MAX). Likewise BOL makes the same comments about text in row. The large value types out of row option is almost the same as text in row, but is for large types like VARCHAR(MAX).

Dan AT Pluralsight DOT COM

andsm,

As far as I know, you are correct that the performance of varchar(max) is
like text with text in row turned on, and you are right to think that this
can affect performance. This is not to say that varchar(max) is worse
than text - it's just that there is a change in the default from text
not in row
to text in row.

In any case, you can get the out-of-row storage by setting the
'large value types out of row' option to ON.  See this article in BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/813586f1-edfe-471e-9338-e55e689a1aaa.htm

If a text column is taking up space in a clustered index, but
you don't really need to access or process it much, except for displaying
small result sets, changing it to varchar(max) without changing the
storage option could make things worse.  If you are using varchar(max)
to replace varchar(8000), in-row storage as the default makes more sense.

My advice is to choose the (max) types now where in the past you have
chosen text, and I would set "large value types out of row" to on unless
you have a good reason not to. The (max) types are easier to work with
in T-SQL, and the only scenario I can think of where you'd want to still
use text (other than backward compatibility with other applications) is to
get some particular setting of the maximum in-row storage. I can't envision
such a scenario (that didn't need a redesign anyway), but the new types
don't allow you to specify a maximum number of bytes to be held in-row.

Varchar(max) may also be useful where you used varchar(some big number)
in the past, but I would be quite a bit more careful about that
scenario, only
because there might be some differences in how the optimizer handles
statistics and query costing of varchar(max).  So far I haven't seen any bad
situations, though.

There are a few quirky (but sensible, if you think about them) things about
varchar(max), too, such as this:

replicate('n',10000) will be an 8000-byte varchar(8000) values, but
replicate(cast('n' as varchar(max)), 10000) will be a 10000-byte
varchar(max)

Steve Kass
Drew University
www.stevekass.com

andsm wrote:

Show quoteHide quote

Returned first row of a column in a group by
Sub Query Problem
LEFT JOIN... WHERE vs AND ?
Optimizing this crazy setup...
How to return rows not existing in another table