Data Types and Column Definitions

When you create a table in a relational database, every column you define must be assigned a data type. The data type tells the database engine what kind of information that column can hold, how much physical storage to allocate for each value, and what operations are valid on that column. Choosing the right data type is one of the most consequential decisions in database design: it prevents nonsensical data from being stored, keeps storage usage efficient, and removes the need to write extensive validation logic in application code. This topic explores every major category of SQL data types, explains how and when to use each one, and shows how they come together inside a CREATE TABLE statement.

Numeric Data Types

Numeric types are divided into two broad families: exact numeric types, which store numbers with perfect precision, and approximate numeric types, which trade a small degree of precision for the ability to represent an enormous range of values.

The most commonly used exact integer type is INT (also written INTEGER). It uses 4 bytes of storage and can represent any whole number from –2,147,483,648 to 2,147,483,647. This range is more than sufficient for most everyday counters, identifiers, quantities, and ages. Consider a column storing the number of items in a warehouse shipment — INT handles that comfortably without wasting space.

SMALLINT uses only 2 bytes and covers the range –32,768 to 32,767. It is an excellent choice when you know with certainty that values will always remain small — for example, a column storing a star rating from 1 to 5, or a column holding the number of seats in a small meeting room. Using SMALLINT instead of INT in such cases cuts storage per row in half for that column. On the other end of the spectrum, BIGINT uses 8 bytes and can store values from roughly –9.2 quintillion to 9.2 quintillion. BIGINT is appropriate for columns such as global transaction identifiers, row counts in very large datasets, or financial ledger sequence numbers where the value might eventually exceed the INT ceiling.

When you need to store numbers that include a decimal point and precision is critical — such as monetary amounts — you should use DECIMAL(p, s) or its synonym NUMERIC(p, s). Here, p is the total precision (the maximum total number of significant digits, both before and after the decimal point) and s is the scale (the number of digits to the right of the decimal point). For example, DECIMAL(10, 2) can store a value such as 12345678.99 — up to 10 significant digits with exactly 2 after the decimal. This type is essential for financial columns because it stores the value exactly as specified, with no rounding error.

FLOAT and REAL are approximate numeric types based on the IEEE floating-point standard. They can represent a vast range of values including extremely small fractions and enormously large numbers, but they introduce tiny rounding errors because they store values in binary form. FLOAT typically uses 8 bytes (double precision) and REAL typically uses 4 bytes (single precision). These types are well-suited for scientific or engineering calculations — storing the result of a physics simulation, a GPS coordinate measurement error, or a machine-learning model output — where a tiny rounding difference has no practical consequence. They should never be used to store currency values, because repeated arithmetic on approximate types can accumulate rounding discrepancies.

Character and String Data Types

String types store textual data. The critical distinction among them is whether the stored length is fixed or variable, and whether a length limit exists at all.

CHAR(n) stores a fixed-length string of exactly n characters. If you insert a value shorter than n, the database pads it with trailing spaces to reach the full length. This behavior may seem wasteful, but it makes CHAR very efficient for columns where every value will always be the same width. Classic examples include two-letter country codes (CHAR(2)), nine-digit postal codes stored uniformly (CHAR(9)), or ISO currency codes (CHAR(3)). Because all values occupy identical storage, certain comparisons and indexing operations can be slightly faster.

VARCHAR(n) stores a variable-length string of up to n characters. The database uses only as much space as the actual content requires, plus a small overhead to record the length. This makes VARCHAR the right choice when values differ substantially in length — full names, email addresses, product descriptions, street addresses. For instance, a VARCHAR(255) column storing the name "Ana" uses only 3 characters of data storage, whereas a CHAR(255) column storing the same value would always consume 255 characters of space.

TEXT (available in PostgreSQL, MySQL, SQLite, and others, though not in all systems) stores large amounts of character data with no enforced length limit, or an extremely large one. It is appropriate for free-form content such as blog post bodies, product review text, legal document text, or log messages. However, TEXT columns are typically excluded from standard indexing in many databases, and some systems impose restrictions on using TEXT in certain expressions, so they should only be used when genuinely large content is expected.

The decision between CHAR and VARCHAR comes down to consistency. If every row in a column will hold a value of the same length, CHAR may be marginally more efficient and self-documenting. If values vary in length, VARCHAR saves space and is almost always preferred.

Date and Time Data Types

Temporal data types store information about points in time. Using proper date/time types — rather than storing dates as plain strings — allows the database to validate dates, perform date arithmetic, sort chronologically, and work with time zones correctly.

DATE stores only the calendar date: year, month, and day. It carries no time-of-day component. A typical stored value looks like '2024-06-15'. DATE is perfect for columns such as a customer's date of birth, an order's delivery date, an employee's hire date, or a contract's expiration date — situations where knowing the specific time of day is irrelevant.

TIME stores only the time of day — hours, minutes, and seconds, sometimes including fractional seconds — with no date component. It suits columns such as a store's daily opening time, a scheduled departure time in a timetable, or a recurring alarm setting.

DATETIME (used in MySQL and SQL Server) and TIMESTAMP (used in PostgreSQL, MySQL, and others) both store a combined date and time value. The difference between them is subtle and varies by database system. In MySQL, DATETIME stores a literal date-and-time value with no time zone conversion and supports a wide range of dates, while TIMESTAMP stores values internally as UTC and converts them to the session's time zone on retrieval — making TIMESTAMP better for recording when events occurred across multiple time zones. In PostgreSQL, you can use TIMESTAMP WITH TIME ZONE (also called TIMESTAMPTZ) for full time zone awareness. These types are commonly used for created_at and updated_at audit columns that record when a row was inserted or last modified.

Many database systems allow a TIMESTAMP column to be defined with DEFAULT CURRENT_TIMESTAMP, so the database automatically records the exact moment of insertion without requiring the application to supply the value explicitly.

Boolean and Binary Data Types

Beyond numbers, text, and dates, databases also need to represent true/false conditions and raw binary content.

BOOLEAN (sometimes written BOOL) stores a logical truth value: TRUE or FALSE. It is the natural choice for flag columns — is_active, is_verified, is_deleted, has_subscription. Using a proper BOOLEAN type rather than storing 1 and 0 in an INT column makes the schema's intent immediately clear and can enable cleaner query expressions. Note that some databases, such as older versions of MySQL, implement BOOLEAN as a synonym for TINYINT(1), so behaviour may vary slightly across systems.

BINARY(n) and VARBINARY(n) are the binary counterparts of CHAR and VARCHAR. BINARY(n) stores exactly n bytes of raw binary data, padding with zero bytes if necessary, while VARBINARY(n) stores up to n bytes using only as much space as the content requires. These types are used for storing fixed or variable-length binary values such as cryptographic hashes (a SHA-256 hash is always 32 bytes, making BINARY(32) ideal), encrypted tokens, or binary-encoded identifiers.

BLOB (Binary Large Object) stores large binary payloads — images, audio files, PDFs, video clips, or any other file content. Just as TEXT is the large-content counterpart of VARCHAR, BLOB is the large-content counterpart of VARBINARY. While storing files directly in the database guarantees transactional consistency and simplifies backup (everything is in one place), it can also increase database size dramatically and put pressure on database memory. Many production systems choose instead to store only a file path or URL in a VARCHAR column, with the actual file living in object storage. The right approach depends on the specific use case and operational requirements.

Choosing Appropriate Data Types for Data Integrity

Data integrity means that the values stored in the database accurately and reliably represent the real-world information they are meant to capture. Data types are the database's first line of defense against invalid data.

When a column is defined as INT, the database will reject any attempt to insert the string "hello" into it — no application-level code needed. When a column is defined as DATE, the database will reject '2024-02-30' because February never has 30 days. When a column is defined as DECIMAL(10, 2), the database ensures the value is numeric and rounds or rejects values that exceed the specified scale. This automatic enforcement is far more reliable than relying on every application layer to validate data correctly before sending it to the database.

Choosing appropriately sized types also matters. A column that stores a person's age in whole years will never exceed 150. Defining it as BIGINT wastes 6 bytes per row compared to SMALLINT. In a table with millions of rows, that difference multiplies into meaningful storage and memory costs. Conversely, using too small a type creates a correctness risk — if a column tracking page view counts is defined as SMALLINT and the count exceeds 32,767, the database will either reject the update or, worse in some systems, silently overflow to a negative number.

Overly permissive types should be avoided when a stricter type fits. Storing a phone number as TEXT instead of CHAR(15) (using the E.164 international standard) allows arbitrarily long or short values that may be invalid. Storing a price as FLOAT instead of DECIMAL(10, 2) introduces subtle rounding errors that compound over thousands of transactions. Every column definition is an opportunity to encode business knowledge directly into the schema.

Defining Columns in a CREATE TABLE Statement

A CREATE TABLE statement defines the table's name and lists each column with its data type and optional constraints. The general syntax for each column follows this pattern:

column_name  data_type  [DEFAULT default_value]  [NULL | NOT NULL]  [other constraints]

Multiple column definitions are separated by commas inside the parentheses. Here is a fully annotated example that brings together all the data type categories covered above:

CREATE TABLE employees (
    employee_id    INT             NOT NULL,
    first_name     VARCHAR(50)     NOT NULL,
    last_name      VARCHAR(50)     NOT NULL,
    gender_code    CHAR(1)         NULL,
    salary         DECIMAL(12, 2)  NOT NULL,
    hourly_rate    REAL            NULL,
    date_of_birth  DATE            NOT NULL,
    hired_at       TIMESTAMP       NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    shift_start    TIME            NULL,
    is_active      BOOLEAN         NOT NULL  DEFAULT TRUE,
    notes          TEXT            NULL,
    profile_photo  BLOB            NULL
);

Walking through this definition column by column:

• employee_id INT NOT NULL — A whole-number identifier that must always be provided. INT is sufficient because a company is unlikely to employ more than 2 billion people.
• first_name VARCHAR(50) NOT NULL — A variable-length name up to 50 characters. VARCHAR is used because names vary in length. NOT NULL means every employee must have a first name recorded.
• gender_code CHAR(1) NULL — A single fixed-width character code. CHAR(1) is efficient when every value is always exactly one character. NULL is permitted because this may be optional.
• salary DECIMAL(12, 2) NOT NULL — An exact monetary value with up to 12 significant digits and 2 decimal places. DECIMAL prevents floating-point rounding errors in financial data.
• hourly_rate REAL NULL — An approximate numeric value for cases where slight rounding is acceptable, here left nullable because not all employees are paid hourly.
• date_of_birth DATE NOT NULL — A calendar date with no time component. NOT NULL enforces that every employee's birthdate is recorded.
• hired_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP — Records the exact date and time the row was inserted. The DEFAULT means the application does not need to supply this value; the database fills it in automatically.
• shift_start TIME NULL — Stores a time of day with no date. Nullable for employees without fixed shifts.
• is_active BOOLEAN NOT NULL DEFAULT TRUE — A flag indicating whether the employee is currently active. Defaults to TRUE so new rows are active unless explicitly marked otherwise.
• notes TEXT NULL — An unrestricted free-text field for comments or additional information. Nullable and uses TEXT because content length is unpredictable.
• profile_photo BLOB NULL — Stores raw binary image data. Nullable because a photo may not always be available.

The DEFAULT keyword deserves special attention. When a row is inserted without specifying a value for a column that has a DEFAULT, the database automatically uses the default value. This is especially useful for audit timestamps (DEFAULT CURRENT_TIMESTAMP), boolean flags (DEFAULT FALSE), and numeric counters (DEFAULT 0). It reduces the amount of data applications must supply and ensures sensible initial values are always in place.

The NULL and NOT NULL constraints work hand-in-hand with data types. A column defined as NOT NULL will cause the database to reject any INSERT or UPDATE that would leave that column without a value, unless a DEFAULT covers the omission. Together, well-chosen data types, appropriate sizes, thoughtful use of NULL vs NOT NULL, and carefully placed DEFAULT values create a schema that accurately models the real world and actively enforces the rules of the business domain it represents.