Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: sql standard varchar size

    i know ms sql allows varchar to reach a size of 8000. i've also read that oracle allows for 4000, though i've never used it and not sure about that. so my question is what is the sql standard for a varchar size?

  2. #2
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: sql standard varchar size

    Originally posted by blur
    i know ms sql allows varchar to reach a size of 8000. i've also read that oracle allows for 4000, though i've never used it and not sure about that. so my question is what is the sql standard for a varchar size?
    MAX for VARCHAR is 8000. However if you have more fields in the same table that will take more space and you cannot actully have column of 8000 since there is record size limit on SQL Server. I guess it is something around 8600.

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    hmm, not using it in a table; just as a local variable. i need to pass in a bunch of stuff in one string, which could be huge. since i can't use text, i'm looking into varchar. 8000 is plenty for my needs. i'm just worried if we make a conversion to another database, this will cause problems.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's because of the size of the datapage...

    sql server went up to an 8k datapage....I think Oracle and DB2 are still at 4k....although DB2 can be set to either 4k or 32k....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: sql standard varchar size

    Originally posted by smasanam
    MAX for VARCHAR is 8000. However if you have more fields in the same table that will take more space and you cannot actully have column of 8000 since there is record size limit on SQL Server. I guess it is something around 8600.
    The size for max row size is 8060 and not 8600 ... You can actually have a column of 8000 bytes provided that the rest of the fields only take up 60 bytes ... Even creating a row of size more than 8060 will not gwnwrate an error but only a warning cautioning that insert or update will fail in case the datasize while entering data into row exceeds 8060.


    The page size is 8192 bytes. 96 bytes are taken by the page header and a few bytes by the row offsets ... but a row cannot span across pages .. thats the reason you cannot have a row greater than 8060 bytes.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Feb 2004
    Posts
    3
    okay so if i'm interested in making my stored procedure compatible to other databases we may use in the future, what would be a "safe" size to set varchar to?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, how verbose is your data-entry staff anyway?

    Set it to 4000 and then you can convert it to unicode if you need to. (Unicode varchar in SQL server is max 4000 characters.)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •