Understanding the Advantages of Using the VARCHAR Data Type

If you have a text column where the number of characters you want to store varies from to row, use a variable-length character string to save disk space. Suppose, for example, that you define an order table as follows:

CREATE TABLE order_table
   (customer_number      INTEGER,
    delivery_date        DATE,
    item_number          SMALLINT,
    quantity             SMALLINT,
    special_instructions CHAR(1000))

By using a fixed CHARACTER type, the DBMS will make the SPECIAL_INSTRUCTIONS column in every row 1,000 characters in length, even if you enter SPECIAL_INSTRUCTION strings for only a few items. As you learned in Tip 23, "Understanding SQL Character Data Types," the DBMS adds blanks to the end of a fixed-length character string if you insert a string with less than the number of characters you define as the string's length—in this case, 1,000 characters. Therefore, if you have one item that requires special instructions in a 10,000-row table, you will waste 9.9MB of disk spaces because the system will store 1,000 blank characters in each of the 9,999 rows that don't have any special instructions.

If on the other hand, you were to create the same ORDER_TABLE using the SQL statement

CREATE TABLE order_table
  (customer_number      INTEGER,
   delivery_date        DATE,
   item_number          SMALLINT,
   quantity             SMALLINT,
   special_instructions VARCHAR(1000))

the DBMS would not add blanks to the character string you insert in the SPECIAL_INSTRUCTIONS column. Thus, for the current example, where only 1 row has SPECIAL_INSTRUCTIONS, your 10,0000-row table will be 9,999,000 bytes (9MB) smaller than the table with identical data whose SPECIAL_INSTRUCTIONS column is declared as a fixed-length character type of 1,000 bytes.

The variable-length data types are:

• VARCHAR
• CHAR VARYING
• CHARACTER VARYING
• NCHAR VARYING
• NATIONAL CHAR VARYING
• NATIONAL CHARACTER VARYING

Review Tip 23 for additional information on how to declare a column using each of these data types.