Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: nvarchar vs varchar

    Hi,

    What is the difference between
    nvarchar nad varchar? Why would I use one and not the other?
    Is the fact that one is Unicode and other isn't the only difference?

    Thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yes...plus nvarchar takes up 2 bytes for every one character

    Sooo thae max for nvarchar is 4000

    unlike varchar(8000)


    Does anyone need unicode?

    Why do people code it when they don't use it.......
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's the only difference, except that since Unicode characters require twice as many bytes to store, a NVarchar can only hold 4000 characters, while Varchar can hold 8000.

    blindman

  4. #4
    Join Date
    May 2003
    Posts
    70
    Thanks,

    I also found out that varchar will only save the length of the string.
    char will save the length of the field specified by you.

    varchar - length 10
    char - length 10

    string - "one"

    varchar will only save 3 chars.
    char will save 10.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, but you only asked the difference between Nvarchar and varchar. Look up datatype in Books Online to make sure you choose the correct type for your data before you put a lot of time into designing tables and writing code.

    blindman

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    In other and older db versions...varchar was a bad thing because of the overhead involved in maintaining the length...

    pretty much now is negligble...

    but for anything around 10 or under, I make'em all char...

    Mainframe DB2 V3 was so bad that they said that if you did have to use them, place them on the end of the row....

    Still would like to know who uses unicode for what it's meant for, and why it's used when it's not

    The coming globalization?
    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.

  7. #7
    Join Date
    May 2003
    Posts
    70
    I see, thanks.

  8. #8
    Join Date
    Mar 2010
    Posts
    2

    Nvarchar

    NVARCHAR allows you to enter data in multiple languages. It has the extended codes necessary for multilingual data.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I use unicode, UTC time, and guids to allow for international (multiple locale) code that has to scale relatively easily... Too often a project that is for "just one department" goes global on me!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2010
    Posts
    2

    Nvarchar

    Yes, we had to go to NVARCHAR2 in Oracle due to our system being installed in France, Poland, China, Brazil, etc. We needed the unicode.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should consider using the new TVarChar datatype in SQL Server 2008, which stores text in a neutral language (Esperanto, I think, I'll have to check the specs), and automatically translates it to the language defined by the user's operating system.
    It's pretty fast, actually, and saves needing to have translation tables for error messages and whatnot.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We've had a lot of success with that on a world wide rollout recently.
    Before 12 where you are eh?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Correction. The TVarChar datatype stores text in MSEsperanto, which is slightly different from ASCII Standard Esperanto. Pretty typical Microsoft behavior...
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    I think I remember a Connect item about some implementations of that working with varchar and the right code page. I'd suggest trying COLLATE Multi_General_CI_AI_KI_2010.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by thewebhosting View Post
    If you set your variable as varchar 50 and add info of 10 characters then remaining space will be added as padding. If you use the nvarchar 50 for the same info then remaining space will be released.
    That's incorrect. VARCHAR has an overhead of two bytes plus a single byte for each character. NVARCHAR has an overhead of two bytes plus two bytes for each character. CHAR has no overhead and will require as many bytes as its length. NCHAR has no overhead and will require twice as many bytes as its length. There's also an ever so slightly complicated overhead for nullable columns.

Posting Permissions

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