locked
varchar and nvarchar which is better? RRS feed

  • Question

  • How do we determine which datatype is better to use. People prefer both depending on the project. Is it safe to use nvarchar(max) becuase it serves both unicode as well as size. I am assuming my database required more varchar(max) columns becuase we dont want to be restrictive. Are there any downsides to the assumption and to use nvarhcar(max)?
    • Moved by Bob Beauchemin Friday, March 12, 2010 12:37 AM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Thursday, March 11, 2010 10:05 PM

Answers

  • If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar.

    Basically you need to decide based on what support and extensibility yoy may provide today and tomorrow.

    Friday, March 12, 2010 5:22 AM

All replies

  • If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar.

    Basically you need to decide based on what support and extensibility yoy may provide today and tomorrow.

    Friday, March 12, 2010 5:22 AM
  • difference :
    http://weblogs.asp.net/guys/archive/2005/01/15/353550.aspx

    if you want to know about the performance
    http://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance

    Friday, March 12, 2010 12:03 PM
  • If your database going to is store multilingual data you should use the nvarchar datatype instead varchar. Also, nvarchar takes twice as much space as varchar data.
    "Vonid" wrote in message news:b0fcc257-a0b0-401d-9dc7-0e3122cf9ad4...
    If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar.

    Basically you need to decide based on what support and extensibility yoy may provide today and tomorrow.

    Cheers
    Vonid

    --- Hope this helps. Ananth Ramasamy Meenachi http://www.msarm.com
    Friday, March 12, 2010 5:30 PM
  • For a datatype, "better" is not a concept that can be answered outside of the context of a given need. The best datatype is the one that fits the need.  I will be brief, as others have given good info too, but there are two parts to the question. Type of data, and length of data:

    Statements like:

    "I am assuming my database required more varchar(max) columns becuase we dont want to be restrictive."

    frightens me. I hope that  all of your columns aren't just declared as (max) just 'cuz'.  I know that some languages don't have a concept of a limited length variable, or at least I have heard that people often don't use them (despite the possible buffer overrun type issues that, again, I have lightly read about), but the if you declare a column as (max), you ought to be willing to at least let them put in 8300 or so characters (or for nvarchar/unicode, 4150).  Otherwise you are just asking for trouble.  Better to limit your type to the size of your data.

    If you are happy to allow unicode, and the programs that are used will accept unicode, then use nvarchar. If you are not going to test unicode characters, and your program may toss it's cookies when someone uses a "funny" character (a very common symptom is seeing a single character looking like a box instead of the character.  Then you are shooting your foot off to be trendy.

    Nvarchar, (max) or otherwise, is very safe and works great.  Takes up more space, but gives you a vastly larger character set you can use without any codepage wierdness.  And if you start having > 8000 characters per row, and you have lots of character columns spilling out off of the database page, you potentially can have tremendous increase in non-sequential disk reads, which will be very harmful to performance.  If you really truly need that capability, there are ways to mitigate the issues, so go for it.
    Louis

    Saturday, March 13, 2010 10:10 AM
  • Just to add here, now SQL 2008 R2 introduced Unicode character compression. If you are using SQL 2008 R2, then compression will help you saving space but need to see other factors:)
    Monday, March 15, 2010 7:34 AM
  • in Short I would suggest to use nvarchar in most cases to avoid code page incompatibilities, becuase varchar is only for 8-bit codepage.

    to avoid compatibility issue nvarchar is prefered ignoring the space factor.

    Hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Saturday, April 7, 2012 7:18 AM
  • Besides all other excellent points mentioned above, there are a couple other things to keep in mind. Storing all variable-width data in (max) column could introduce a couple issues.

    First, you cannot add (max) column as the key column to the index.

    Second, for variable width columns, SQL Server estimates 50% population when it calculates row size / memory grant for the query. For (max) column SQL Server estimates 4,000 bytes. Underestimation and overestimation both are not good in terms of performance. Underestimation can lead to sort/hash in tempdb. Overestimation can lead to excessive memory usage as well as waiting for the memory grants. From pure performance standpoint it could make sense to define column size 2 times bigger than expected data size is, although such kind of optimization needs to be carefully considered because it compromises logical data model. You can see the example here: http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/


    Thank you!

    My blog: http://aboutsqlserver.com


    Saturday, April 7, 2012 2:19 PM