This guide walks through each category with examples, shows where MySQL, SQL Server, Oracle and PostgreSQL differ, and ends with a short checklist for choosing a type. If you work in Excel every day, we point out where a SQL type matches something you already know from a spreadsheet.
TL;DR
- Every column has one data type. It controls what the column can store, how much space it takes and how the database validates input.
- The main groups are numeric, character (text), date and time, Boolean and binary, plus engine-specific extras such as JSON, UUID and ENUM.
- Use exact types (DECIMAL or NUMERIC) for money and approximate types (FLOAT, DOUBLE) only for measurements where tiny rounding differences are acceptable.
- Type names are not identical across engines. Oracle uses VARCHAR2 and NUMBER, SQL Server uses BIT for flags, and MySQL treats BOOLEAN as a small integer.
- LogicExcel is free to start: the Excel test and the first two practice lessons are free, and the rest sits in All-Access.
Table of Contents
What a SQL data type does
In a table, each column is declared with a type. The type decides which values the database accepts, how it stores them and which operations make sense on them. You can add two integers, but adding a date and a name makes no sense, and the engine will either refuse or convert one value to the other.
That conversion is where bugs hide. Microsoft's documentation explains that when an operator combines two expressions of different types, the result type follows data type precedence rules, so a number mixed with text may quietly turn into something you did not intend. Declaring the right type up front is the cheapest form of data validation you have.
The spreadsheet parallel is familiar. In Excel a cell holding "00123" as text behaves differently from the number 123, and a date typed as text will not sort or subtract correctly. A SQL column makes that distinction permanent for every row.
The main categories of SQL data types
The SQL standard and every major engine organize types into broadly the same families. SQL Server's documentation lists seven categories: exact numerics, approximate numerics, date and time, character strings, Unicode character strings, binary strings and a group of other types such as xml, json and uniqueidentifier. Other engines fold these into fewer groups. The table below uses the five groups most beginners meet first.
| Category | What it stores | Common types |
| Numeric | Whole numbers, exact decimals, approximate decimals | INT, SMALLINT, BIGINT, DECIMAL, NUMERIC, FLOAT, DOUBLE |
| Character | Text of fixed or variable length | CHAR, VARCHAR, TEXT |
| Date and time | Calendar dates, clock times, timestamps | DATE, TIME, DATETIME, TIMESTAMP |
| Boolean | True or false flags | BOOLEAN, BIT |
| Binary | Files, images, raw bytes | BLOB, BYTEA, VARBINARY |
The next sections take each in turn.
Numeric types
Numeric types split into three jobs: whole numbers, exact decimals and approximate decimals.
Integers
INT (or INTEGER), SMALLINT and BIGINT hold whole numbers. They differ only in range. In SQL Server, for example, int covers about plus or minus 2.1 billion and bigint goes far beyond it, so a row ID that may pass two billion needs BIGINT from the start. A column such as quantity or employee_id is a natural fit for an integer type.
Exact decimals: DECIMAL and NUMERIC
DECIMAL(p,s) and NUMERIC(p,s) store exact values. The first number is the total digits (precision) and the second is the digits after the decimal point (scale). A price column declared as DECIMAL(10,2) holds values such as 67400.90 and rounds nothing silently. In SQL Server, decimal and numeric are identical.
Approximate decimals: FLOAT, REAL and DOUBLE
FLOAT, REAL and DOUBLE PRECISION are fast and cover a huge range, but they are inexact. The PostgreSQL manual describes real and double precision as inexact, variable-precision types that follow the IEEE 754 standard, so some values cannot be stored exactly and may come back slightly different. They suit sensor readings, coordinates and scientific measurements.
Money needs exact types
The same PostgreSQL page recommends the numeric type for currency amounts and other quantities that require exactness. Oracle's MySQL team gives the same direction for its engine: DECIMAL for prices and currency, FLOAT or DOUBLE for scientific measurements. Accountants know the pain of a total that is off by a cent, and a float column is one way to get it.
Character types
Text columns come in fixed-length and variable-length flavors.
- CHAR(n) is fixed length. If you store a shorter value, the database pads it with spaces. In SQL Server, char(n) always uses n bytes), with n from 1 to 8,000. It fits values that are always the same size, such as two-letter state codes like NY.
- VARCHAR(n) is variable length. Its storage is the actual data plus 2 bytes, per the same SQL Server page). It fits names, email addresses and street names such as 5th Avenue.
- TEXT holds large blocks, such as product descriptions or free-form notes. Limits vary by engine, so check your own database's manual before relying on a size.
The guidance on that SQL Server page is simple: use char when entries are a consistent size, varchar when sizes vary considerably, and varchar(max) when they may exceed 8,000 bytes. For sites that support several languages, it points to Unicode types, nchar and nvarchar, to avoid character conversion problems.
One trap for spreadsheet users: store ZIP codes, phone numbers and account numbers as text, not as integers. They are labels, not quantities, and a ZIP code like 02134 would lose its leading zero in a numeric column. Excel users know the same fix as formatting a column as Text before pasting.
Date and time types
Date and time types let the database sort chronologically, subtract one date from another and filter by period.
- DATE stores year, month and day, such as 1983-02-03.
- TIME stores hours, minutes and seconds, such as 11:23:45.
- DATETIME and TIMESTAMP store both together, such as 1983-03-03 11:23:45. Which one to use depends on the engine, because they handle time zones and ranges differently.
The year-month-day order is not a coincidence. A public-affairs data journalism tutorial from an academic source notes that SQL date and time values follow
ISO 8601, because country and locale formats differ, and a US-style 3/4/2026 is ambiguous to someone in another country. Writing dates as
2026-03-04 removes the doubt.
Engines differ in the details. Oracle's DATE, for instance, covers January 1, 4712 BC to December 31, 9999 AD and, unlike the DATE in most other engines, carries a time of day. If you are used to Excel's date serial numbers, our guides to Excel date functions and time functions show the same split between a day and a time of day, and the TIME functions practice lets you try it in a sheet.
Boolean and binary types
A Boolean column holds true or false. Support is uneven:
- PostgreSQL has a native BOOLEAN type, and Oracle's current data types reference lists BOOLEAN too.
- SQL Server uses BIT, which stores 0 or 1, as its documentation says for Boolean values.
- MySQL accepts BOOLEAN but treats it as a small integer, with 1 for TRUE and 0 for FALSE.
Binary types store raw bytes such as images or PDF files: BLOB in MySQL and Oracle, BYTEA in PostgreSQL, VARBINARY in SQL Server. Many teams store the file elsewhere and keep only its path in a text column, which keeps the table small.
NULL is not a type, but it matters
Every column can also hold NULL, the marker for "no value". The data journalism tutorial cited above stresses that NULL is not the same as an empty string or the number 0 and needs different syntax in filters (IS NULL, not = NULL). If you want to practice that kind of condition, the NOT operator exercise is a short one.
How data types differ across MySQL, SQL Server, Oracle and PostgreSQL
The concepts carry over, but the spelling does not. This comparison draws on each engine's own documentation.
| Need | MySQL | SQL Server | Oracle | PostgreSQL |
| Whole number | INT, BIGINT | int, bigint | NUMBER | integer, bigint |
| Exact decimal | DECIMAL | decimal, numeric | NUMBER(p,s) | numeric |
| Variable text | VARCHAR | varchar | VARCHAR2 | varchar, text |
| Flag | BOOLEAN (small integer) | bit | BOOLEAN | boolean |
| Binary | BLOB | varbinary | BLOB | bytea |
Sources:
MySQL categories,
SQL Server,
Oracle and
PostgreSQL.
A few differences cause the most trouble when you move a query between systems:
- Oracle uses VARCHAR2 for variable-length text and NUMBER for numbers, with precision from 1 to 38, according to its reference.
- PostgreSQL ships extras such as jsonb, uuid, timestamptz and arrays, all listed in its data types chapter.
- MySQL adds ENUM and SET for a fixed list of allowed values, as the MySQL team's overview describes.
- SQL Server separates Unicode text into nchar and nvarchar, and groups xml, json and rowversion under other types.
When you copy a table definition from one system to another, check each type against the target engine's manual instead of assuming the name carries over.
Examples of SQL data types in a real table
Here is a small invoices table that uses most of the types above. The syntax is standard enough to work in MySQL and PostgreSQL with minor edits.
CREATE TABLE invoices (
invoice_id BIGINT,
customer_name VARCHAR(90),
state_code CHAR(2),
amount DECIMAL(10,2),
issued_on DATE,
created_at TIMESTAMP,
is_paid BOOLEAN,
notes TEXT
);
Reading it column by column shows the reasoning:
- invoice_id is a BIGINT so the counter never runs out.
- customer_name is VARCHAR because names vary in length.
- state_code is CHAR(2) because every value is exactly two letters.
- amount is DECIMAL(10,2) so cents stay exact.
- issued_on is a DATE, and created_at is a TIMESTAMP because the exact moment matters for auditing.
- is_paid is a flag, and notes is free text.
An accountant who rebuilds reports every month will notice the parallel: each column is like a spreadsheet column formatted once, correctly, for every row. To practice reading and writing queries like these, start with the
SQL basics and SELECT exercises, then try the
fundamentals review.
How to choose the right data type
Work through these questions for each column.
- Is it a quantity you will calculate with? Use a numeric type. If it is money, use DECIMAL or NUMERIC. One alternative is storing whole cents in an INTEGER, so 1999 stands for 19.99.
- Is it an identifier or code? Use text if you never do arithmetic on it, and use CHAR only if the length never changes.
- Is it a date, a time or both? Use a date or timestamp type and never store dates as text. Write literal values in ISO 8601 order.
- Is it a yes/no flag? Use the engine's Boolean or bit type.
- How large can it grow? Pick the smallest type that safely covers the range, then leave headroom. Changing a type on a big table later is slow and risky.
- Will the text be multilingual? On SQL Server, use the Unicode types. The Microsoft guidance above recommends them for sites with several languages.
Two habits save time. First, keep the type consistent across tables, so a customer ID is the same type everywhere it appears and joins do not force hidden conversions. Second, write the type down in a short data dictionary, the way you would label columns in a well-built workbook.
Our perspective: types are the same discipline you already use in Excel
> People who are good at spreadsheets are usually closer to SQL than they think. When you format a column as a date, keep a ZIP code as text or round a price to two decimals, you are making data type decisions. SQL simply makes them explicit and permanent.
>
> At LogicExcel we build practice around that bridge. You type real formulas into a sheet, every answer is marked cell by cell, and a different route to the right answer still counts. Every wrong answer comes with a reason. We think that is how data habits stick: you see the result of your choice at once, not a video of someone else's.
>
> If your work is heading toward databases, the spreadsheet fundamentals come first. Clean text, correct dates and exact numbers carry over directly, and the lessons you finish stay open for you to revisit.
>
> Thomas Houza
Build the spreadsheet foundations that make SQL easier
LogicExcel is free to start. The Excel test (12 real tasks, marked, with answers) is free, and so are the first two practice lessons. The 5-day accounting course by email is free as well, and you can join the email course here. To see where you stand, take the Excel test.
The rest of LogicExcel is in All-Access: the 95 practice lessons, the Excel I to III courses with their timed exam and certificate, Academy Essentials, the Excel for Accounting course and the template library. All-Access plans cost $29 a month, $99 a year ($8.25 a month) or a lifetime plan. Lessons you have finished stay open. For SQL itself, you can also browse the SQL practice exercises and the guide to SQL practice exercises with solutions.
FAQ
What are the main data types in SQL?
The main groups are numeric (INT, DECIMAL, FLOAT), character (CHAR, VARCHAR, TEXT), date and time (DATE, TIME, TIMESTAMP), Boolean and binary. Engines add their own extras, such as JSON, UUID and ENUM. SQL Server's documentation, for example, splits its types into seven categories.
What is the difference between CHAR and VARCHAR?
CHAR is fixed length and pads shorter values with spaces, while VARCHAR stores only what you enter. In SQL Server, varchar adds 2 bytes to the actual data length). Use CHAR for values that are always the same size, such as two-letter state codes, and VARCHAR for names and emails.
Which SQL data type should I use for money?
Use DECIMAL or NUMERIC with a fixed scale, such as DECIMAL(10,2). PostgreSQL's manual recommends numeric for currency amounts because float types can return slightly different values from what you stored. Storing whole cents in an integer is another exact option.
Are SQL data types the same in MySQL, SQL Server and Oracle?
The categories match, but names and limits differ. Oracle uses VARCHAR2 and NUMBER, SQL Server uses bit for flags, and MySQL treats BOOLEAN as a small integer. Check the manual for your engine before you copy a table definition.
Should I store dates as text?
No. A DATE or TIMESTAMP column sorts correctly, supports date arithmetic and rejects impossible dates such as February 30. If you must write a date literal, use the ISO 8601 order, year then month then day.
Sources
Recommended
