will I burn in hell for using all VARCHAR(MAX)?

Discussion in 'ASP General' started by mike, Mar 19, 2007.

  1. mike

    mike Guest

    so I keep optimizing my fields down to the minimum character length
    necessary i.e., varchar(15), then I find out a month later its gotta
    get bigger, then a few months later, bigger again, etc. Nowadays on
    sql server 2005 and on, how bad is it really to use varchar(max)? Is
    there really a big performance or storage hit or is it negligible?

    -Mike
    mike, Mar 19, 2007
    #1
    1. Advertising

  2. mike wrote:
    > so I keep optimizing my fields down to the minimum character length
    > necessary i.e., varchar(15), then I find out a month later its gotta
    > get bigger, then a few months later, bigger again, etc. Nowadays on
    > sql server 2005 and on, how bad is it really to use varchar(max)? Is
    > there really a big performance or storage hit or is it negligible?
    >
    > -Mike


    Don't be lazy. There are several reasons for not doing this in the
    comments for this blog article:
    http://sqljunkies.com/WebLog/simons/archive/2006/02/28/Why_use_anything_but_varchar_max.aspx
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 19, 2007
    #2
    1. Advertising

  3. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > mike wrote:
    > > so I keep optimizing my fields down to the minimum character length
    > > necessary i.e., varchar(15), then I find out a month later its gotta
    > > get bigger, then a few months later, bigger again, etc. Nowadays on
    > > sql server 2005 and on, how bad is it really to use varchar(max)? Is
    > > there really a big performance or storage hit or is it negligible?
    > >
    > > -Mike

    >
    > Don't be lazy. There are several reasons for not doing this in the
    > comments for this blog article:
    >

    http://sqljunkies.com/WebLog/simons/archive/2006/02/28/Why_use_anything_but_varchar_max.aspx


    Hmm.. Interesting. I'm not sure some of the commenters were making a
    distinction between varchar(somevalue) and varchar(max)? They seemed to be
    comparing varchar with char. Others were concerned unexpected growth but if
    there is a cause for that in the lifetime of a DB then the alternative is
    errors downstream where data entered doesn't fit in the defined field.
    Personally I'd rather see things slow down instead of break.

    I agree it does seem a bit lazy though.
    Anthony Jones, Mar 19, 2007
    #3
  4. Anthony Jones wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> mike wrote:
    >>> so I keep optimizing my fields down to the minimum character length
    >>> necessary i.e., varchar(15), then I find out a month later its gotta
    >>> get bigger, then a few months later, bigger again, etc. Nowadays on
    >>> sql server 2005 and on, how bad is it really to use varchar(max)?
    >>> Is
    >>> there really a big performance or storage hit or is it negligible?
    >>>
    >>> -Mike

    >>
    >> Don't be lazy. There are several reasons for not doing this in the
    >> comments for this blog article:
    >>

    > http://sqljunkies.com/WebLog/simons/archive/2006/02/28/Why_use_anything_but_varchar_max.aspx
    >
    >
    > Hmm.. Interesting. I'm not sure some of the commenters were making a
    > distinction between varchar(somevalue) and varchar(max)?


    I'm not sure what you were looking at. all the comments seemed to be on
    target.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Mar 20, 2007
    #4
  5. "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Anthony Jones wrote:
    > > "Bob Barrows [MVP]" <> wrote in message
    > > news:...
    > >> mike wrote:
    > >>> so I keep optimizing my fields down to the minimum character length
    > >>> necessary i.e., varchar(15), then I find out a month later its gotta
    > >>> get bigger, then a few months later, bigger again, etc. Nowadays on
    > >>> sql server 2005 and on, how bad is it really to use varchar(max)?
    > >>> Is
    > >>> there really a big performance or storage hit or is it negligible?
    > >>>
    > >>> -Mike
    > >>
    > >> Don't be lazy. There are several reasons for not doing this in the
    > >> comments for this blog article:
    > >>

    > >

    http://sqljunkies.com/WebLog/simons/archive/2006/02/28/Why_use_anything_but_varchar_max.aspx
    > >
    > >
    > > Hmm.. Interesting. I'm not sure some of the commenters were making a
    > > distinction between varchar(somevalue) and varchar(max)?

    >
    > I'm not sure what you were looking at. all the comments seemed to be on
    > target.
    >


    The final comment by werner de jong seems to be entirely based on the
    difference between char and varchar.

    Also others references performance issues. I'm wondering where there is a
    cost in performance between varchar(x) and varchar(max)?

    Could be that I'm ignorant of some new details of 2005 internals. What is
    the difference internally in the way these fields are handled.

    Belsteak says 'unpredictable growth of the DB' and 'unwanted results in
    column', what does that mean??

    Anthony.
    Anthony Jones, Mar 20, 2007
    #5
  6. Anthony Jones wrote:
    >>> Hmm.. Interesting. I'm not sure some of the commenters were making a
    >>> distinction between varchar(somevalue) and varchar(max)?

    >>
    >> I'm not sure what you were looking at. all the comments seemed to be
    >> on
    >> target.
    >>

    >
    > The final comment by werner de jong seems to be entirely based on the
    > difference between char and varchar.


    No, I believe he was talking about the impact of having many varchar columns
    as opposed to a few. He is likely assuming that at least some of the
    varchar(max) columns could be char, since the blog post he was replying to
    made no reference to leaving existing char columns alone. Or using
    varchar(max) to store other data types.

    >
    > Also others references performance issues. I'm wondering where there
    > is a
    > cost in performance between varchar(x) and varchar(max)?


    I think Adam Machanic's article covers this: "Remember that the query
    optimizer uses column size as one of the many metrics for determining
    optimal query plans. Given this table, the optimizer would have very few
    options in that regard. "

    >
    > Could be that I'm ignorant of some new details of 2005 internals.
    > What is
    > the difference internally in the way these fields are handled.
    >


    Again, from Adam's article:
    The MAX data types, by default, use a hybrid of the TEXT/IMAGE overflow
    behavior and the behavior of the normal (sized) VARCHAR/VARBINARY types. If
    a column's data, plus the data in all of the other columns in the table, has
    a total size of less than 8060 bytes, the data is stored in-row. If the data
    exceeds 8060 bytes, the data in the MAX column will be stored off-row.

    > Belsteak says 'unpredictable growth of the DB' and 'unwanted results
    > in
    > column', what does that mean??
    >


    With varchar(max) one cannot be sure how much data is going to be stored in
    it. Thus database growth cannot be predicted, making any forecasts of
    database size meaningless. Also, every time a record's size exceeds a page
    (8000 b), the record gets split. Every time the split occurs, performance
    suffers, both for maintaining and for reading the data. Since we are not
    controlling how much data gets stored, we cannot predict how many splits
    will occur. "Unwanted results" I believe is referring to the lack of control
    of data size removing one key validation method for preventing unwanted
    data.

    Many of these arguments are the same ones used to counter the old "why not
    make every column a varchar(8000) column" proposal in the SQL2000 days.


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Mar 20, 2007
    #6
  7. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Anthony Jones wrote:
    > >>> Hmm.. Interesting. I'm not sure some of the commenters were making a
    > >>> distinction between varchar(somevalue) and varchar(max)?
    > >>
    > >> I'm not sure what you were looking at. all the comments seemed to be
    > >> on
    > >> target.
    > >>

    > >
    > > The final comment by werner de jong seems to be entirely based on the
    > > difference between char and varchar.

    >
    > No, I believe he was talking about the impact of having many varchar

    columns
    > as opposed to a few. He is likely assuming that at least some of the
    > varchar(max) columns could be char, since the blog post he was replying to
    > made no reference to leaving existing char columns alone. Or using
    > varchar(max) to store other data types.


    I see. I didn't read it that way. It seems to me the blogger is talking
    about varchar(n) vs varchar(max).

    >
    > >
    > > Also others references performance issues. I'm wondering where there
    > > is a
    > > cost in performance between varchar(x) and varchar(max)?

    >
    > I think Adam Machanic's article covers this: "Remember that the query
    > optimizer uses column size as one of the many metrics for determining
    > optimal query plans. Given this table, the optimizer would have very few
    > options in that regard. "


    That's a good point. Statistics are not by default built for varchar(max)
    fields but I believe they can be specified.

    >
    > >
    > > Could be that I'm ignorant of some new details of 2005 internals.
    > > What is
    > > the difference internally in the way these fields are handled.
    > >

    >
    > Again, from Adam's article:
    > The MAX data types, by default, use a hybrid of the TEXT/IMAGE overflow
    > behavior and the behavior of the normal (sized) VARCHAR/VARBINARY types.

    If
    > a column's data, plus the data in all of the other columns in the table,

    has
    > a total size of less than 8060 bytes, the data is stored in-row. If the

    data
    > exceeds 8060 bytes, the data in the MAX column will be stored off-row.


    Cool. A pragmatic solution. I like.

    >
    > > Belsteak says 'unpredictable growth of the DB' and 'unwanted results
    > > in
    > > column', what does that mean??
    > >

    >
    > With varchar(max) one cannot be sure how much data is going to be stored

    in
    > it. Thus database growth cannot be predicted, making any forecasts of
    > database size meaningless.


    Using varchar(max) doesn't mean you haven't got a reasonable idea of the
    size of data going into the field. It means you don't want to limit the size
    that might end up in there. I'm being devils adovacate here, for small
    fields, titles, descriptions, names etc I wouldn't use varchar(max) either
    (I agreed it is lazy) but for anything over say 512 I would have been
    tempted but:-

    > Also, every time a record's size exceeds a page
    > (8000 b), the record gets split. Every time the split occurs, performance
    > suffers, both for maintaining and for reading the data. Since we are not
    > controlling how much data gets stored, we cannot predict how many splits
    > will occur.


    Ouch. Yes that's a killer reason.

    > "Unwanted results" I believe is referring to the lack of control
    > of data size removing one key validation method for preventing unwanted
    > data.


    Good point.

    >
    > Many of these arguments are the same ones used to counter the old "why not
    > make every column a varchar(8000) column" proposal in the SQL2000 days.
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    Anthony Jones, Mar 25, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lisa
    Replies:
    2
    Views:
    410
  2. hokiegal99

    HDD Burn In with Python

    hokiegal99, Dec 29, 2003, in forum: Python
    Replies:
    9
    Views:
    3,648
    Neil Hodgson
    Dec 31, 2003
  3. Aldo Ceccarelli
    Replies:
    1
    Views:
    248
    Weinhandl Herbert
    Apr 16, 2009
  4. SpreadTooThin
    Replies:
    3
    Views:
    869
    SpreadTooThin
    Apr 29, 2009
  5. jcpc
    Replies:
    2
    Views:
    1,447
Loading...

Share This Page