<< Home

Chapter 5: Data Types and Storage

Every piece of data in a relational database has a defined type that determines what values it can hold and how they are stored internally. Choosing the correct types improves precision, saves space, and helps the database process queries efficiently. This chapter introduces the major categories of SQL data types (numeric, string, date/time, and special types) and explains how to select the right ones for your tables.

💡 Always assign an explicit type to every column. It makes your schema self-documenting and helps prevent subtle bugs caused by type conversions.

Numeric, string, date/time, and special types

The SQL standard defines several broad families of data types. Each database vendor implements them with small variations, but the concepts remain the same. The four most common categories are numeric, character, temporal, and special-purpose types.

Numeric types

Numeric types represent numbers used in calculations, identifiers, and measurements. They can be exact or approximate.

INTEGER (or INT): Whole numbers, typically 32-bit.
BIGINT: Larger integers for identifiers and counters.
DECIMAL(p,s) or NUMERIC(p,s): Exact fixed-point values with precision p and scale s.
FLOAT or REAL: Approximate floating-point numbers for scientific or statistical data.

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT,
  price    DECIMAL(8,2),
  quantity INT
);

Use DECIMAL or NUMERIC for currency values to avoid rounding errors, and integer types for counts or IDs.

⚠️ Floating-point types are faster but can produce tiny rounding differences. For financial applications, never store prices as FLOAT.

String types

Character data types store text, identifiers, and other symbolic data. The main distinction is between fixed and variable length.

CHAR(n): Fixed-length text padded with spaces if shorter than n.
VARCHAR(n): Variable-length text up to n characters.
TEXT or CLOB: Large, unbounded text values (nonstandard length).

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(100),
  email VARCHAR(200) UNIQUE
);

Choose a length that fits your data comfortably without wasting space. VARCHAR is the usual default for user input and short strings.

💡 When defining VARCHAR limits, base them on expected real data rather than arbitrary round numbers. This makes validation easier and signals intent to future developers.

Date and time types

Temporal types represent calendar dates, times of day, or combined timestamps. They are crucial for tracking when events occur and for performing time-based queries.

DATE: Year, month, and day.
TIME: Time of day (optionally with fractional seconds).
TIMESTAMP: Combined date and time.
INTERVAL: Duration (such as “3 days” or “2 hours”).

CREATE TABLE orders (
  order_id   INTEGER PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  shipped_at TIMESTAMP
);

Use date functions like NOW(), CURRENT_DATE, and AGE() (PostgreSQL) to work with temporal values. Storing times in UTC makes it easier to convert and compare across regions.

⚠️ Databases differ in time zone handling. Some store timestamps with zone offsets, others assume the server’s local time. Always check documentation before relying on automatic conversions.

Special types

Modern SQL engines support additional specialized types for structured and binary data:

BOOLEAN: Logical true/false values.
BLOB: Binary large object, for files or images.
JSON: Structured documents in JavaScript Object Notation.
UUID: Universally unique identifiers.
ARRAY (in PostgreSQL and others): Lists of elements of the same type.

CREATE TABLE settings (
  id      INTEGER PRIMARY KEY,
  active  BOOLEAN DEFAULT TRUE,
  config  JSON,
  file    BLOB
);

Special types allow you to store complex or non-tabular data without breaking the relational model. However, they should be used carefully to avoid portability issues between different database engines.

⚠️ JSON support varies widely. Some systems store it as text, while others use binary formats and allow direct indexing. Write portable code by using JSON for flexible metadata, not core relational data.

Choosing correct types for efficiency and precision

Efficient database design depends as much on choosing the right types as on writing good queries. Smaller and more precise types save space, speed up joins, and make indexing faster.

Match type to purpose

Choose the simplest type that correctly expresses the data’s meaning. If a value is always whole, use an integer rather than a floating point. If a value can be absent, decide whether NULL is acceptable or if a default should apply.

-- Better: explicit types and constraints
CREATE TABLE inventory (
  item_id   INTEGER PRIMARY KEY,
  quantity  SMALLINT CHECK (quantity >= 0),
  restocked DATE DEFAULT CURRENT_DATE
);

Consider storage and indexing

Larger data types consume more disk space and can slow queries. For instance, using BIGINT where INTEGER would suffice doubles the index size without benefit. Similarly, overly large VARCHAR columns can waste memory and disk bandwidth.

💡 Databases store indexes separately from table data. Smaller key sizes make indexes faster to scan and fit better in memory.

Precision and scale in decimals

When defining DECIMAL(p,s) or NUMERIC(p,s), choose precision and scale that fit realistic data ranges. For currency, two decimal places usually suffice. Overly large precision can slow arithmetic operations.

-- Price up to 999999.99
price DECIMAL(8,2)

This definition stores six digits before the decimal point and two after, using a fixed binary representation for exactness.

Use defaults wisely

Defaults supply values automatically when none are provided, reducing repetitive input. Use them for predictable data such as creation timestamps, flags, or counters.

CREATE TABLE logs (
  log_id   INTEGER PRIMARY KEY,
  created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  active   BOOLEAN   DEFAULT TRUE
);

⚠️ Defaults should express intent, not replace missing data silently. If a value is truly optional, let it be NULL instead of substituting a placeholder.

Portability considerations

Different database engines have slightly different names or behaviors for equivalent types. Here are some common equivalents:

Concept	ANSI SQL	MySQL	PostgreSQL	SQLite
Auto-increment integer	`GENERATED AS IDENTITY`	`AUTO_ INCREMENT`	`SERIAL`	`INTEGER PRIMARY KEY`
Fixed text	`CHAR(n)`	`CHAR(n)`	`CHAR(n)`	`TEXT`
Variable text	`VARCHAR(n)`	`VARCHAR(n)`	`VARCHAR(n)`	`TEXT`
Date/time	`TIMESTAMP`	`DATETIME`	`TIMESTAMP`	`TEXT` (ISO format)

Understanding these differences will help you design schemas that are easy to port between systems or share across teams using different engines.

By selecting appropriate data types and storage options, you lay the foundation for efficient, maintainable databases that behave predictably under any workload.