Should I use nVarchar or Varchar..?

  • Joy Smith San

    SSC-Insane

    Points: 24877

    Dear All,

    While creating tables, few people in my office always select NVARCHAR as datatype.can anyone tell me the advantages and disadvantages of using NVARCHAR...

    Wot's the diff between nVarchar nad Varchar..?

    Which is more better..?

    Pelase guide..

    Santhosh Nair.

  • Greg Charles

    SSC-Forever

    Points: 45403

    Nvarchar, nchar, and ntext are unicode datatypes that are designed to cover all the characters of all languages, so they should be used in databases that are used internationally, particularly if data will be modified by users of East Asian languages.

    Storage in unicode uses twice the space as non-unicode characters. If the data isn't shared internationally, unicode datatypes generally aren't needed.

    See "Storage and Performance Effects of Unicode" in BooksOnLine.

    Greg

  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    Hi There,

    Firstly I would recommend using BOL as the explanations are pretty good.

    However seeing as though I'm here I'll try.

    OK Firstly they both have different lengths.

    NVARCHAR tends to take twice as much 'room' for each charater

    e.g

    [font="Courier New"]

    DECLARE @Nvar NVARCHAR(10)

    DECLARE @var VARCHAR(10)

    SELECT

       @Nvar = REPLICATE (N'a',20)

       ,@var = REPLICATE ('a',20)

    SELECT

       @Nvar AS [Nvar],

       @var AS [Var],

       LEN(@Nvar) AS [Nvar Len],

       LEN(@var) AS [var Len],

       DATALENGTH(@Nvar) AS [Nvar DLen],

       DATALENGTH(@var) AS [var DLen][/font]

    This is because NVARCHAR holds unicode charaters.

    So the MAX you can store in a NVARCHAR is 4000 where in a VARCHAR it's 8000, this excludes the MAX storage of VARCHARS.

    HOPE that helps.

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Bhuvnesh

    SSC Guru

    Points: 59344

    In simple language....whenever you have special characters use nvarchar otherise always use varchar datatype

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • GabyYYZ

    SSCertifiable

    Points: 7913

    Even more importantly, I recommend for sanity purposes in the future, you come up with a formal process whether or not to use varchar/nvarchar in tables. This keeps things civil between different departments and you don't swear at them underneath your breath. 🙂 (on a separate, come up with a consistent collation for all your databases if you can).

    Gaby
    ________________________________________________________________
    "In theory, theory and practice are the same. In practice, they are not."
    - Albert Einstein

  • allan.ford17

    SSC Eights!

    Points: 930

    Does anyone have data examples? e.g. what is allowed and not allowed in each of varchar and nvarchar ?
    e.g. for a surname ... should nvarchar be used ? or varchar ?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    allan.ford17 - Wednesday, December 26, 2018 11:59 PM

    Does anyone have data examples? e.g. what is allowed and not allowed in each of varchar and nvarchar ?
    e.g. for a surname ... should nvarchar be used ? or varchar ?

    Here is a query that shows you the difference.
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @SAMPLE_SIZE  INT = 65535;
    ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
    , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
         FROM T T1,T T2,T T3,T T4,T T5,T T6)
    SELECT
      NM.N
     ,CHAR(NM.N) AS VCHAR
     ,NCHAR(NM.N) AS NVCHAR
    FROM  NUMS  NM
    ORDER BY NM.N ASC;

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715079

    Eirikur's query lets you see characters that are allowed in each. In general, if you stick with Western languages and data, varchar is fine.

    The important thing is that you don't want those varchar <-> nvarchar implicit conversions between your app and the database as they can cause some performance problems. Anyone querying the data should be sure their parameter datatypes match.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply