3 Replies Latest reply: Oct 28, 2011 2:24 AM by user12240205 RSS

    Dear Gurus: Can u pls explain the difference between VARCHAR2 & NVARCHAR2??

    user12240205
      Dear Gurus,

      Can you please explain in simple terms to us Newbies the difference between VARCHAR2 and NVARCHAR2.

      I read all documentation but I just don't get it??

      What exactly is the advantage of NVARCHAR2?

      When should we use it???

      What are the differences?

      Is NVARCHAR2 used only when using non-English character sets???

      Is there a space saving advantage???

      Some say NVARCHAR2 will give different values to VARCHAR2 columns when using LENGTH function???

      Thanks in Advance

      Edited by: user12240205 on Oct 27, 2011 6:15 AM

      Edited by: user12240205 on Oct 27, 2011 6:15 AM
        • 1. Re: Dear Gurus: Can u pls explain the difference between VARCHAR2 & NVARCHAR2??
          Dom Brooks
          Essentially, if the characterset of your database (NLS_CHARACTERSET from NLS_DATABASE_PARAMETERS) does not support the characters/encodings that you require then NLS_NCHAR_CHARACTERSET and NVARCHAR2 and NCLOB offer you an alternative.

          Otherwise you're faced with rebuilding your database with the characterset you need, which imho is the best approach if possible.
          • 2. Re: Dear Gurus: Can u pls explain the difference between VARCHAR2 & NVARCHAR2??
            Sergiusz Wolicki-Oracle
            The CHAR data types (VARCHAR2, CHAR, LONG, CLOB) store data in the database character set. The NCHAR data types (NVARCHAR2, NCHAR, NCLOB) store data in the national character set. The national character set can be either AL16UTF16 (default) or UTF8 (rare compatibility requirements). The database character set may be any of tens of character sets supported by Oracle. The recommended database character set is AL32UTF8.

            Both AL16UTF16 and AL32UTF8 are Unicode encodings -- UTF-16BE and UTF-8, correspondingly.

            The advantages of NCHAR data types:

            - They are guaranteed Unicode data types, that is, any database since Oracle 9.0 can store Unicode data in NVARCHAR2, NCHAR, and NCLOB columns.
            - Unicode storage of South and East Asian languages is more compact in AL16UTF16 compared to AL32UTF8. AL16UTF16 storage is possible only in NCHAR data types.

            The (serious!) disadvantages of NCHAR data types:

            - You need special coding in client access APIs to make sure that data you want to store in NCHAR data type columns does not go through conversion to the database character set, losing the "guaranteed Unicode" advantage.
            - There are Oracle components that do not support NCHAR data types, most notably Oracle Text and XDB.
            - It is confusing and error-prone to work with two database character sets, the database character set and the national character set.
            - Storage of most European languages is more compact in AL32UTF8 compared to AL16UTF16.

            Oracle's advice:

            - For any new database, create it with the AL32UTF8 character set and forget about NCHAR data types.
            - For any existing application to be made multilingual, migrate the backend database to AL32UTF8 and forget about NCHAR data types.
            - For any existing non-Unicode database serving a large legacy application system that is too costly or impossible to migrate to Unicode, to which you are asked to add a minor module that has to support multilingual data and for which a separate database makes little sense, you may consider NVARCHAR2 columns for this multilingual data.


            -- Sergiusz
            • 3. Re: Dear Gurus: Can u pls explain the difference between VARCHAR2 & NVARCHAR2??
              user12240205
              Thanks Gurus, I now understand much better.