Pierce Technical
SQL lessons9 of 38

Data types

A column's type decides what it can hold and how it compares, sorts and adds up.

Beginner8 min readLesson 9 of 38
In this lesson 7 sections
  1. The types you’ll meet most
  2. Store a value by what it is, not how it looks
  3. Numbers: precision and scale
  4. Text compares one character at a time
  5. BOOLEAN: three possible values
  6. Dates
  7. Snowflake notes
Jump to practice

Every column has a data type. Knowing it tells you which filters and functions will work, and explains a lot of strange results.

The types you’ll meet most

Kind of data Most databases SQL Server Use it for
Whole numbers INTEGER INT, BIGINT IDs, counts, quantities
Exact decimals DECIMAL(12,2) or NUMERIC(12,2) DECIMAL(12,2) Money, rates, anything you add up
Approximate decimals FLOAT FLOAT Measurements. Never money
Text VARCHAR VARCHAR, NVARCHAR Names, codes, emails, zip codes
True / false BOOLEAN BIT (1 / 0) Flags such as is_won
Date DATE DATE A day with no time: close_date
Date and time TIMESTAMP DATETIME2 The moment something happened: closed_at

Store a value by what it is, not how it looks

Zip codes, phone numbers and account numbers look like numbers but are text. You never do math on them, and a number type drops leading zeros: 01234 becomes 1234.

Numbers: precision and scale

DECIMAL(12,2) means 12 digits in total, 2 of them after the decimal point. Values with more decimals get rounded to fit.

FLOAT stores approximations, which is why this happens:

SELECT 0.1 + 0.2 = 0.3;   -- can be FALSE with FLOAT values

Use exact decimal types for money so totals reconcile to the cent.

Text compares one character at a time

SELECT '10' < '9';           -- TRUE: '1' sorts before '9'
SELECT 10 < 9;               -- FALSE: compared as numbers

If numbers sort strangely (1, 10, 2, 20), the column is probably text.

Text comparison is case-sensitive in PostgreSQL and SQLite, so 'Enterprise' = 'enterprise' is FALSE there. Compare LOWER(segment) = 'enterprise' to be safe.

BOOLEAN: three possible values

is_won is TRUE, FALSE, or NULL while the deal is still open.

WHERE is_won              -- won deals (same as is_won = TRUE)
WHERE NOT is_won          -- lost deals only: open deals (NULL) drop out
WHERE is_won IS NULL      -- still open

SQL Server has no BOOLEAN. It uses BIT, so you’d write WHERE is_won = 1.

Dates

Write dates as 'YYYY-MM-DD'. That format works on every major database and sorts correctly as text too.

WHERE close_date >= '2026-07-01'

Remember the half-open range from lesson 4 for timestamp columns like closed_at.

Snowflake notes

Kind of data Snowflake
Whole numbers INTEGER (stored as NUMBER(38,0))
Exact decimals NUMBER(12,2)
Date and time TIMESTAMP_NTZ (no time zone), TIMESTAMP_LTZ, TIMESTAMP_TZ
JSON from APIs VARIANT, OBJECT, ARRAY

Text comparison is case-sensitive in Snowflake, so compare LOWER(segment) = 'enterprise' there too. A VARCHAR’s length is a limit, not a storage cost: VARCHAR(20) and plain VARCHAR take the same space.

Try it

Return the name and amount of every lost deal. Open deals (where is_won is NULL) must not appear.

Write it in the sandbox below, then press Check answer.

SQL sandbox

Run queries against the sample CRM tables (accounts, opportunities,users, contacts and opportunity_history), right in your browser. Nothing you run leaves your computer.

Back to the top of the lesson