SQL Data Types

Every column in a table is declared with a data type, and the database enforces it: try to store text in a numeric column and it's rejected or silently converted, depending on the database. Picking the right type up front avoids a surprising amount of pain later, wasted storage, broken sorting, and values that don't compare the way you expect.

Learning Objectives

• Identify the main categories of SQL data types: numeric, text, date/time, and boolean.
• Choose an appropriate type for a given column instead of defaulting to text for everything.
• Recognize where type names genuinely differ between MySQL, PostgreSQL, SQLite, and SQL Server.

The Main Categories

Numeric Types

Whole numbers use an integer type, and numbers with decimal places use a fixed-precision type (exact, good for money) or a floating-point type (approximate, good for scientific values where tiny rounding error doesn't matter). The keywords differ slightly by database, but the concepts are identical everywhere.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  salary DECIMAL(10,2)
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  salary NUMERIC(10,2)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  salary DECIMAL(10,2)
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  salary NUMERIC
);
-- SQLite uses "type affinity" rather than strict types,
-- NUMERIC still stores salary as an exact value here.

Text Types

VARCHAR is the default choice for most text columns, names, emails, titles. Reach for TEXT when a column genuinely has no practical size limit, like a blog post body or a product description.

CREATE TABLE employees (
  first_name VARCHAR(100),
  bio TEXT
);

CREATE TABLE employees (
  first_name TEXT,
  bio TEXT
);
-- SQLite stores all text as TEXT regardless of a declared length,
-- VARCHAR(100) is accepted as valid syntax but the 100 isn't enforced.

Date and Time Types

Boolean Values

PostgreSQL and SQL Server have a real boolean type (BOOLEAN in PostgreSQL, BIT in SQL Server, storing 0 or 1). MySQL and SQLite don't, MySQL's BOOLEAN is just an alias for TINYINT(1), and SQLite stores booleans as the integers 0 and 1 with no dedicated type at all. The practical effect is the same everywhere: a two-state column that your application treats as true/false.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use DECIMAL/NUMERIC for any value involving money, never FLOAT or DOUBLE.
• Default to VARCHAR with a sensible length for short text, reserve TEXT for genuinely unbounded content.
• Use a real date/time type (not text) on every database that offers one, so the database validates and sorts dates correctly.
• Pick the smallest numeric type that comfortably fits your data's range, it saves storage at scale without any downside.

Interview Questions

Summary

This lesson covered the main SQL data type categories, numeric, text, date/time, and boolean, and where their exact names and behavior genuinely differ between MySQL, PostgreSQL, SQL Server, and SQLite's more lenient type affinity system.

What's Next?

With data types covered, the next lesson starts writing data: INSERT, adding new rows to a table.