SQL Data Types

SQL data types define what kind of value a column can hold, how much storage it uses, and which operations make sense on it. Common categories are numeric types (INT, DECIMAL, FLOAT), string types (VARCHAR, CHAR, TEXT), date and time types, and BOOLEAN; exact DECIMAL types suit money, while approximate FLOAT types do not.

3 min read

Every column in a SQL table is declared with a data type, which determines what kind of value it can hold, how much storage it uses, and what operations make sense on it. Getting data types right at design time avoids real bugs later, like accidentally being able to store text in a column meant for a date, or losing precision on a value that needed to be exact.

The common categories

  1. Numeric types: INT (whole numbers), BIGINT (larger whole numbers), DECIMAL(p, s) or NUMERIC(p, s) (exact fixed-point numbers, essential for money), FLOAT/DOUBLE (approximate floating-point numbers, not safe for money due to rounding behavior).
  2. String types: VARCHAR(n) (variable-length text up to n characters), CHAR(n) (fixed-length text, padded if shorter), TEXT (long, effectively unbounded text).
  3. Date and time types: DATE (just a calendar date), TIME (just a time of day), TIMESTAMP (date and time together, often with timezone awareness).
  4. Boolean: BOOLEAN, true or false.
  5. Other: JSON/JSONB in databases like PostgreSQL for storing semi-structured data directly in a column, UUID for globally unique identifiers.

A worked example: getting money types right

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(150),
    price DECIMAL(10, 2),
    in_stock BOOLEAN,
    added_on DATE
);

INSERT INTO products VALUES (1, 'Wireless Mouse', 24.99, true, '2024-03-01');

price is declared DECIMAL(10, 2), meaning up to 10 total digits with exactly 2 after the decimal point, guaranteeing 24.99 is stored and compared exactly as written. If price had instead been declared as a FLOAT, repeated arithmetic on it (adding tax, applying a discount, summing many rows) could accumulate tiny rounding errors that eventually show up as a wrong total, a real, recurring class of bug in systems that mistakenly use floating-point types for currency.

Where this matters in real systems

Financial and e-commerce systems are strict about using DECIMAL/NUMERIC for any monetary column specifically because of this rounding issue, floating-point types are genuinely unsafe for money at scale. Meanwhile, analytics and scientific systems often prefer FLOAT/DOUBLE for large-scale numeric computation where approximate precision is an acceptable tradeoff for speed and storage.

What interviewers actually ask about SQL data types

  • "Why shouldn't you store money in a FLOAT column?" Floating-point types are approximate, and repeated arithmetic can accumulate small rounding errors that eventually produce a wrong total; DECIMAL/NUMERIC store exact values instead.
  • "What's the difference between CHAR and VARCHAR?" CHAR is fixed-length and pads shorter values with spaces; VARCHAR is variable-length and only uses as much space as the actual value needs.
  • "What's DECIMAL(10, 2) mean?" Up to 10 total digits, with exactly 2 of them after the decimal point.
  • "When would you use TEXT instead of VARCHAR?" When the content has no reasonable fixed maximum length, like a blog post body or a long user comment.

Key takeaways

  • SQL data types define what values a column can hold and how they're stored and compared.
  • DECIMAL/NUMERIC store exact values and are the correct choice for money; FLOAT/DOUBLE are approximate and can introduce rounding errors.
  • VARCHAR is variable-length text, CHAR is fixed-length and padded, TEXT is for unbounded long text.
  • Choosing the right data type at design time prevents real correctness bugs later, not just a storage optimization.
All 174 DBMS interview questions

Log in to save which topics you've read across every subject.

Frequently asked questions

Why shouldn't you store money in a FLOAT column?

Floating-point types are approximate, and repeated arithmetic can accumulate small rounding errors that eventually produce a wrong total; DECIMAL/NUMERIC store exact values instead.

What's the difference between CHAR and VARCHAR?

CHAR is fixed-length and pads shorter values with spaces; VARCHAR is variable-length and only uses as much space as the actual value needs.

What's DECIMAL(10, 2) mean?

Up to 10 total digits, with exactly 2 of them after the decimal point.

When would you use TEXT instead of VARCHAR?

When the content has no reasonable fixed maximum length, like a blog post body or a long user comment.