Difference between CHAR and VARCHAR

Help
1

As per memsql docs, we should use VARCHAR instead of CHAR since CHAR takes 3*length. Could somebody answer the below questions.

  1. Is there any difference between using CHAR and VARCHAR other than the storage size. We are migrating from a Db2 database on mainframe and most of our keys are CHAR in Db2. Will there be any issue if we convert these to VARCHAR in memsql.

  2. Following is my assumption of the length for CHAR vs VARCHAR.

    CHAR - 3*Length, VARCHAR - 7 bytes for pointer + Length of the VARCHAR + 4 bytes for storing the length. But if the length of the VARCHAR is less than 7 bytes, then it is directly stored in the 7 bytes pointer.

     LENGTH            CHAR                   VARCHAR
 1                 3 bytes                8 bytes
 2                 6 bytes                8 bytes
 3                 9 bytes                8 bytes
 4                 12 bytes               8 bytes
 5                 15 bytes               8 bytes
 6                 18 bytes               8 bytes
 7                 21 bytes               7+8+4 - 19 bytes
 8                 24 bytes               8+8+4 - 20 bytes

    So should we start using VARCHAR if the length is greater than 2 bytes to not waste storage ?

Thanks.

2

Hi,

  1. I’m not familiar with any quirks of DB2, but in MemSQL, outside the difference in storage, the other main differences between CHAR and VARCHAR are:
  • Trailing spaces are automatically trimmed for CHAR (and not for VARCHAR). This is something we picked up from being MySQL compat.
  • Varchars are allocated “off row” so there is extra overhead to inserting a row with many varchars (we need to allocate memory for the varchar columns separately from memory for the fixed length types stored on the row). This is typically not very noticeable unless you have 100s of varchars and are inserting into a table with has very few indexes.
  1. That’s correct.