Home Forums SQL Server 2005 Development Difference between varchar(max) and varchar(8000) RE: Difference between varchar(max) and varchar(8000)

  • December 14, 2012 at 6:51 am

    #1568674

    chetan.aegis (12/14/2012)

    sql server 2005 annot exceed 8000 bytes in size.variable binary column can store 8000 bytes assuming in a table.And were as var char can store 2 bytes per Unicode character. The actual storage size of the var char(max) is length of data entered + 2.VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

    Not quite right. First (if we're going to deal with exact byte counts), the VARCHAR(MAX) datatype holds 2^31-1 bytes. A couple of entires into a scientific calculator says the actual answer is 2,147,483,647. You might have just phat phingered that last digit.

    I'm also not sure what you mean by "sql server 2005 annot [sic] exceed 8000 bytes in size" because 2005 was the first version to come out with the MAX datatype capabilities. That includes VARCHAR, NVARCHAR, and VARBINARY. CHAR, NCHAR, and BINARY can only hold 8000 bytes each with the understanding that that will only be 4000 characters in NCHAR.

    There's something else (and I may have missed it above)... The use of any "blob" datatype (which includes any datatype defined as MAX, XML, IMAGE, ect) will prevent online rebuilds of indexes if the "blob" column(s) are contained within the index in 2005 and 2008. That means that you won't be able to rebuild the Clustered Index of a table if the table contains a "blob" column. Of course, any Non-Clustered index with an "include" on a "blob" column will suffer the same fate. IIRC, they've fixed this "little" problem in 2012.

    --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)