Prevent errors from mismatched types

Storage efficiency

Types determine how much disk space each value uses.

Data integrity

Only valid operations are allowed (math on numbers only).

Sort order

Numbers sort numerically; text sorts alphabetically.

Query performance

Indexes work best when types match the data.

What happens when you store a number as VARCHAR instead of INTEGER?

One quick way to verify a column is stored as a number is to use it in arithmetic. If a column is really an integer, adding or subtracting will work correctly.

SELECT
  product,
  total_cost ___ ___ AS unit_price
FROM orders

Store and compute with numeric data

An INTEGER stores whole numbers -- numbers without decimal points. Think of it like a counter that only shows complete units: 1, 2, 3, not 1.5.

SELECT
  test_name,
  value ___ ___ AS total
FROM ab_results

INTEGER is the workhorse of SQL schemas. It supports the full range of arithmetic operations and sorts numerically, making it ideal for counts, ranks, and quantities.

BIGINT provides a larger range at the cost of double the storage. Use it when values can exceed 2.1 billion, such as globally unique identifiers or high-volume event counters.

Handle text of varying lengths

VARCHAR stores text -- a sequence of characters (often called a "string"). Unlike integers which have a fixed size, text values vary in length: "Hi" is 2 characters while "Hello World" is 11.

SELECT
  username,
  ___ ___ contact_email
FROM users

VARCHAR is the most common string type because it stores only the actual content, not a fixed allocation. This efficiency matters at scale where millions of rows with variable-length strings add up quickly.

When sizing VARCHAR columns, audit your actual data first. Choosing VARCHAR(50) for a field that only ever holds 10 characters wastes both index space and makes comparisons slower.

Represent yes/no decisions in data

A BOOLEAN represents logical truth values: TRUE or FALSE. Think of it like a light switch -- it can only be on or off, nothing in between.

TRUE and FALSE are reserved SQL keywords, not strings. You will learn to combine them with logical operators in later lessons.

Using BOOLEAN properly improves both storage efficiency and code clarity.

SELECT
  user_id,
  ___ AS ___
FROM users

Boolean columns consume only 1 byte each. When a table has many flag columns, using BOOLEAN instead of VARCHAR or INTEGER significantly reduces both storage and join key sizes.

SQL BOOLEAN can also be NULL, representing an unknown state. This three-valued logic (true, false, unknown) is important to handle correctly in WHERE clauses and aggregations.

Convert values between types safely

CAST explicitly converts values from one type to another. This is essential when data is stored in the wrong type or when you need to perform type-specific operations.

Apply CAST to convert between common types in these practice exercises.

SELECT
  ___(price AS ___)
FROM products

CAST is explicit and safe when you know the data is valid. For untrusted data from external sources, TRY_CAST is preferable because it returns NULL on failure instead of crashing the query.

Type conversions happen at query time and have a cost. When the same CAST is needed repeatedly, consider storing the converted value in a dedicated column during the ETL process.

Mastering CAST and data types together means you can work confidently with data from any source, whether it arrives with the correct types or needs cleanup before analysis.