CHAR or VARCHAR?

  • Hi,

    what are precise design considerations for choosing either CHAR or VARCHAR?

    I know that char(10) uses exactly 10 bytes. I know that varchar uses less than 10 bytes if the string is say 5 characters long. But how much exactly?

    I'd guess that the engine needs to store some delimiter (that one or two bytes) or some value that tracks the actual length of the string (say a smallint with 2 bytes).

    And: Is there an overhead in memory management when using varchar that influences performance significantly? If I use a char and change the string no memory needs to be allocated whereas with varchar it should be necessary...

    Thank you in advance

    Alexander

  • It's a 2 byte overhead for storing the length (although that's not exactly what is stored on the pages)

    There's no memory impact for expanding varchars, but it can cause page splitting if the varchar column grows and the page is full.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you for your reply.

    So, what are your decision rules? (Like the maximum string length being greater than x and the variance of the strings' length being greater than y)

  • If all the strings in the table are going to be more or less the same length (within say 10 characters, depending on size) then char, otherwise varchar.

    Things like ID numbers which are 13 characters long (south african ones anyway) or NULL are perfect for char. Postal codes, telephone numbers, much the same. Names vary too much and I'll make then varchar.

    There aren't precise rules for when which should be used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • My personal preference is to use varchar whenever I am not guaranteed a certain length. The examples Gail has given are good example if you are working on a database for a single country. My current project is international in scope so things like phone, postal code, and national id vary so I used varchar, but in a US only system I would have gone with char except for zip code since it could have the +4.

    I know I have a read other places that you should use char when the max length is 10 or less.

Viewing 5 posts - 1 through 4 (of 4 total)

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