Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Red face Unanswered: nchar or char or nvarchar or varchar???

    Hi,

    Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???

    Also, what really is the benefit of one over the over

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: nchar or char or nvarchar or varchar???

    Originally posted by Joozh
    Hi,

    Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???

    Also, what really is the benefit of one over the over

    Thanks
    the difference between char and varchar is that: char is a fixed length datatype meaning, if suppose u have char(8) and you store a value say 'Harsh' in this variable then it gets stored a 'Harsh___' where at the right the remaining space is padded with blanks, in short all the 8 bits are utilised.
    whereas if it would have been a varchar(8) it would have saved it as 'Harsh' meaning only the exact required size is allocated which is 5 in this case.
    When you want to store the data in unicode format use nchar or nvarchar .

  3. #3
    Join Date
    Oct 2003
    Posts
    163
    Thanks harshal_in :-)

    That clarifies.... One last quick question:

    Is it okay to assume then that the best approach is to use nvarchar OR varchar?

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    nvarchar uses double the amount of storage compared to varchar, so use varchar unless you need to store double byte data (eg japanese, chinese)

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by Joozh
    Thanks harshal_in :-)

    That clarifies.... One last quick question:

    Is it okay to assume then that the best approach is to use nvarchar OR varchar?
    Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

    If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance.

    Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are some advantages to using the CHAR and NCHAR types, but those advantages are somewhat esoteric. If you want more information see Kalen Delaney's Inside SQL Server 2000 (which I see as a "must read" for a SQL geek anyway).

    Basically it boils down to bookkeeping. Variable length columns require the DBE (data base engine) to compute both the start and the length of the variable columns. This adds considerable overhead at the row manager level, which makes access to all of the columns in a row with variable width columns take longer. This isn't significant in most cases, but it does matter for bulk loads and similar operations. Particularly for staging very large warehouses, it can be significant.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But the size of the row can be just as much a performance factor...

    For example Char(255) will store all of that...now think about returning all the data, as comp[ared to what is just there...

    I beleive a rule of thumb is something like 10 chars...

    10 or less make char, otherwise varchar...

    Pat?
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is actually more complicated than that, but you could approximate the rule by using: If the maximum column length minus the minimum column length is more than 10 characters, then use a variable length column. I still say you should just read the book so you'll understand the gist of the rule (and 10,000 other important things) and the factors that weigh into it, but the approximation is a lot better than nothing.

    -PatP

Posting Permissions

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