Character data types in SQL are essential for storing text-based information. You will frequently encounter three main types: CHAR, VARCHAR, and TEXT. Imagine CHAR as a row of lockers, each locker always the same size, whether you fill it completely or not. VARCHAR is more like adjustable shelves, growing or shrinking to fit the items you place on them, up to a maximum size. TEXT is a storage room with no rigid upper limit, designed for very large amounts of text. Understanding these differences helps you choose the right type for your data and optimize your database.

CREATE TABLE product_info (
    code CHAR(5),
    description VARCHAR(100),
    notes TEXT
);

In the product_info table above, each column uses a different character data type to highlight their strengths. The code column uses CHAR(5), which means every value will be stored using exactly five characters. This is efficient for fixed-length codes, such as product IDs or postal codes. The description column uses VARCHAR(100), allowing any length up to 100 characters. This flexibility suits names or short descriptions that can vary in length. The notes column uses TEXT, which is ideal for long, free-form comments or detailed information, since it can hold much more data than CHAR or VARCHAR.

From a storage perspective, CHAR always reserves the specified number of characters, so short values are padded with spaces to fit the length. VARCHAR only uses as much space as needed for each entry, plus a small amount of overhead to track the length. TEXT is optimized for very large values, often stored outside the main table row, and is best for data that exceeds the typical limits of VARCHAR. In terms of performance, CHAR can be faster for fixed-size values, but wastes space if data is often shorter than the defined length. VARCHAR balances space efficiency and flexibility, making it a common choice for variable-length fields. TEXT is perfect for storing articles, comments, or any content that might be too large for VARCHAR, but may have some limitations in indexing or performance compared to smaller types.

INSERT INTO product_info (code, description, notes) VALUES
('A123', 'Widget', 'This is a basic widget.'),
('B4567', 'Gadget', 'Gadgets come in many varieties. Some notes can be very long and detailed, exceeding the usual limits of VARCHAR columns.'),
('C89', 'Thingamajig', NULL);