Home Forums SQL Server 2005 SQL Server 2005 General Discussion Altering column from varchar(2048) to varchar(max) performance RE: Altering column from varchar(2048) to varchar(max) performance

  • July 29, 2009 at 4:24 pm

    #1032063

    ALZDBA (7/29/2009)

    altering to varchar(8000) is only a catalog operation (and maybe an index op if the column is indexed) because it stays the same datatype, only the var length changes.

    Changing to datatype varchar(max) is actually a change to an other datatype ! (max characteristics)

    This also drives the data to an "out of row" condition. It has to find a reference and then go outside the row and page to find the data. All of that takes extra time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)