SQL Server Performance

Exact Difference between varchar and nvarchar

Discussion in 'General Developer Questions' started by Sitanshu, Feb 12, 2006.

  1. Sitanshu New Member

    Hi All,

    I want to know the exact difference between Varchar and nVarchar.
    If possible please give me the advantages and disadvantages of their usage.

    Thanking You

    Sitanshu

    Sitanshu Gupta
  2. Madhivanan Moderator

    Read about them in SQL Server help file

    Madhivanan

    Failing to plan is Planning to fail
  3. Sitanshu New Member

    I dont have the help file...thats y i wrote over here.

    Sitanshu Gupta
  4. Madhivanan Moderator

    From BOL,


    char and varchar
    Fixed-length (char) or variable-length (varchar) character data types.

    char[(n)]

    Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

    varchar[(n)]

    Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

    Remarks
    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.

    Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:

    Use char when the data values in a column are expected to be consistently close to the same size.


    Use varchar when the data values in a column are expected to vary considerably in size.
    If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar.

    When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.




    nchar and nvarchar
    Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

    nchar(n)

    Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

    nvarchar(n)

    Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

    Remarks
    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    Use nchar when the data entries in a column are expected to be consistently close to the same size.

    Use nvarchar when the data entries in a column are expected to vary considerably in size.

    Objects using nchar or nvarchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause.

    SET ANSI_PADDING OFF does not apply to nchar or nvarchar. SET ANSI_PADDING is always ON for nchar and nvarchar.



    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

    Btw, the Books Online are available online. You can read them over at the MS site. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  6. dineshasanka Moderator

  7. pravesh singh New Member

  8. Luis Martin Moderator

    Pravesh: Welcome to the forums!!.:)
    Kindly check thread date. This one is 7 years old.
  9. ashishsharma050982 New Member

Share This Page