Difference between varchar(max) and varchar(8000)

  • sattusarmaha

    SSC Rookie

    Points: 27

    Hi Charles,

    If Gail Shaw is wrong, can u pls giv me answer of below query -

    Declare @a as NVARCHAR(max)

    SET @a = replicate(convert(NVARCHAR(MAX), '¢¢¢¢¢¢¢¢¢¢'), 805)

    select @a

    select len(@A)

    GO

    Why @a gets more then 8000 ?

    Satyanarayan V.

  • Mad Myche

    SSCommitted

    Points: 1573

    sattusarmaha (3/1/2011)


    Hi Charles,

    If Gail Shaw is wrong, can u pls giv me answer of below query -

    Declare @a as NVARCHAR(max)

    SET @a = replicate(convert(NVARCHAR(MAX), '¢¢¢¢¢¢¢¢¢¢'), 805)

    select @a

    select len(@A)

    GO

    Why @a gets more then 8000 ?

    Satyanarayan V.

    Read the entire post, Charlesz said the max was 64k.

    Director of Transmogrification Services
  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please note: 2 year old thread

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 996619

    GilaMonster (3/3/2011)


    Please note: 2 year old thread

    Heh... yeah, but it's still fun. 😀

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Mad Myche

    SSCommitted

    Points: 1573

    Oh well... The only reason I came to it was the newsletter had it in it... Still learned from it so it's all good

    Director of Transmogrification Services
  • jasonmorris

    SSCrazy

    Points: 2492

    Now....

    Who has the biggest Pork Chop Cannon?

    Jeff perhaps?

  • Jeff Moden

    SSC Guru

    Points: 996619

    Nah... no pork chops required on this friendly little thread. 🙂

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • RogueBlackSheep

    Valued Member

    Points: 63

    when talking with rookies:

    - teach them proper t-sql (not addon products!)

    - show them ref online (msdn, http, books online, etc)

    - do not confuse them with "products", show them code!!!

    you are right displaying products there is a form of SPAM

    please teach the rookies correctly, good habits from the start

  • haiao2000

    Hall of Fame

    Points: 3734

    DAMN!!!...I STILL DON'T KNOW WHAT PORK CHOP HAS TO DO WITH SQL. and it is alright that Microsoft Certified Master MVP be wrong once awhile, just like Microsoft once was wrong when it thought it could bring apple down to its knees 🙂

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please note: 3 year old thread (and what I was in this thread was incorrectly accused of being wrong. I've been wrong elsewhere, but not here)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • haiao2000

    Hall of Fame

    Points: 3734

    GilaMonster (11/28/2012)


    Please note: 3 year old thread (and what I was in this thread was incorrectly accused of being wrong. I've been wrong elsewhere, but not here)

    I was aware of the aging thread...but just like Jeff said...it was still fun to read 🙂 ...at the same time checking to see if everyone still alive, many things could happen in 3 years 🙂

  • Jeff Moden

    SSC Guru

    Points: 996619

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • praveshsinghfaq

    SSC Enthusiast

    Points: 134

    Hi!

    You can get the help from the following link. I hope it very helpful for you.

    http://www.mindstick.com/Blog/479/Difference%20between%20char%20var

    Thanks & Regard!

    Pravesh Singh

  • Jeff Moden

    SSC Guru

    Points: 996619

    praveshsinghfaq (5/6/2013)


    Hi!

    You can get the help from the following link. I hope it very helpful for you.

    http://www.mindstick.com/Blog/479/Difference%20between%20char%20var

    Thanks & Regard!

    Pravesh Singh

    It helps but not with the subject at hand because it doesn't explain any of the MAX datatypes, which was the subject of this entire post. 😉

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • praveshsinghfaq

    SSC Enthusiast

    Points: 134

    Difference between Varchar(8000) and Varchar(MAX)

    Varchar(8000) stores a maximum of 8000 characters, and

    Varchar(MAX) stores a maximum of 2,147,483,647 characters

    It supported in SQL Server 2005 or above version

Viewing 15 posts - 31 through 45 (of 57 total)

You must be logged in to reply to this topic. Login to reply