Column data types (INT, VARCHAR, DATE, DECIMAL) in SQL - Deep Dive

Overview - Column data types (INT, VARCHAR, DATE, DECIMAL)

What is it?

Column data types define what kind of information each column in a database table can hold. For example, INT stores whole numbers, VARCHAR stores text, DATE stores calendar dates, and DECIMAL stores precise numbers with decimals. These types help the database understand how to store, organize, and use the data correctly. Choosing the right type ensures data is accurate and efficient to work with.

Why it matters

Without data types, databases would not know how to handle or validate the data, leading to errors, wasted space, and slow searches. For example, mixing numbers and text in the same column would confuse calculations or sorting. Data types protect data quality and make databases fast and reliable, which is essential for everything from websites to banking systems.

Where it fits

Before learning data types, you should understand what a database and tables are. After mastering data types, you can learn about constraints, indexing, and writing queries that use these types effectively.

Mental Model

Core Idea

Data types are labels that tell the database what kind of data each column holds, guiding storage, validation, and operations.

Think of it like...

Think of data types like different containers in a kitchen: a jar for spices (small text), a bottle for oil (numbers with decimals), a calendar for dates, and a box for whole fruits (integers). Each container is designed to hold a specific kind of item safely and neatly.

┌───────────────┐
│   Table Row   │
├───────────────┤
│ ID (INT)      │  ← Whole numbers, like 1, 2, 3
│ Name (VARCHAR)│  ← Text, like 'Alice'
│ Birthdate (DATE)│ ← Dates, like 2024-06-01
│ Salary (DECIMAL)│  ← Precise numbers, like 1234.56
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding INT for Whole Numbers

Concept: INT stores whole numbers without decimals.

INT is used when you want to store numbers like 1, 100, or -50. It cannot hold fractions or text. For example, a column for counting items or IDs uses INT.

Result

The database stores only whole numbers in INT columns, rejecting decimals or text.

Knowing INT only accepts whole numbers helps prevent errors when storing counts or identifiers.

2

Foundation

Using VARCHAR for Text Data

Concept: VARCHAR stores variable-length text strings.

VARCHAR holds words or sentences like names or addresses. You specify a maximum length, like VARCHAR(50), meaning up to 50 characters. It saves space by only using what is needed.

Result

Text data fits efficiently in VARCHAR columns, with a limit on length.

Understanding VARCHAR's length limit helps avoid truncating important text or wasting space.

3

Intermediate

Storing Dates with DATE Type

Concept: DATE stores calendar dates in a standard format.

DATE columns hold values like '2024-06-01'. The database understands this as a date, allowing you to compare, sort, or calculate durations easily.

Result

Dates are stored consistently and can be used in date calculations.

Knowing DATE stores real dates prevents errors from storing dates as text, which would break date operations.

4

Intermediate

Precision with DECIMAL Type

Concept: DECIMAL stores numbers with exact decimal places.

DECIMAL is for numbers like money where exact decimals matter, e.g., 123.45. You define total digits and decimal places, like DECIMAL(7,2) means 7 digits total, 2 after the decimal.

Result

Numbers with decimals are stored precisely without rounding errors.

Understanding DECIMAL prevents mistakes in financial or scientific data where precision is critical.

5

Intermediate

Choosing Data Types for Columns

🤔Before reading on: Do you think using VARCHAR for numbers is okay or problematic? Commit to your answer.

Concept: Choosing the right data type affects data integrity and performance.

Using VARCHAR for numbers can cause problems like wrong sorting or failed calculations. Using INT or DECIMAL ensures numbers behave correctly. Similarly, storing dates as text breaks date functions.

Result

Proper data types lead to accurate data and efficient queries.

Knowing why data types matter helps you design tables that work well and avoid subtle bugs.

6

Advanced

Impact of Data Types on Storage and Speed

🤔Before reading on: Does using a bigger data type always improve performance? Yes or no? Commit to your answer.

Concept: Data types affect how much space data uses and how fast queries run.

INT uses less space than VARCHAR for numbers, so queries on INT columns are faster. Using overly large VARCHAR wastes space and slows down searches. DECIMAL uses more space than INT but is needed for precision.

Result

Choosing efficient data types improves database speed and storage.

Understanding storage impact guides you to balance precision and performance.

7

Expert

Subtle Effects of Data Types on Indexing and Sorting

🤔Before reading on: Do you think sorting a VARCHAR column with numbers stored as text sorts numerically or alphabetically? Commit to your answer.

Concept: Data types influence how indexes work and how sorting behaves.

VARCHAR columns sort alphabetically, so '10' comes before '2'. INT columns sort numerically, so 2 comes before 10. Indexes on INT columns are smaller and faster. Using wrong types can cause unexpected query results.

Result

Correct data types ensure indexes and sorting behave as expected.

Knowing this prevents subtle bugs in query results and improves performance.

Under the Hood

Internally, the database allocates storage space based on the data type. INT uses fixed bytes to store whole numbers efficiently. VARCHAR stores text with a length prefix and variable bytes. DATE stores dates as numbers counting days from a base date. DECIMAL stores numbers as scaled integers to keep exact decimal places. The database uses these formats to validate input, optimize storage, and speed up operations like sorting and searching.

Why designed this way?

Data types were designed to balance storage efficiency, speed, and correctness. Fixed-size types like INT allow fast math and indexing. Variable types like VARCHAR save space for text. DATE and DECIMAL handle special needs like calendar calculations and precise decimals. Alternatives like storing everything as text were rejected because they cause errors and slow performance.

┌───────────────┐
│   User Input  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data Type Check│
│ (INT, VARCHAR, │
│  DATE, DECIMAL)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Storage Format │
│ - INT: fixed  │
│ - VARCHAR: var│
│ - DATE: number│
│ - DECIMAL: int│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Query Engine  │
│ Uses types to │
│ validate,     │
│ sort, index   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think VARCHAR columns sort numbers numerically or alphabetically? Commit to your answer.

Common Belief: VARCHAR columns sort numbers the same way as INT columns.

Reality: VARCHAR columns sort text alphabetically, so '10' comes before '2'. INT columns sort numbers numerically.

Why it matters: Sorting numbers stored as text leads to wrong order, causing bugs in reports or user interfaces.

Quick: Can you store fractional numbers accurately in INT columns? Commit to yes or no.

Common Belief: INT columns can store decimal numbers by rounding or truncating.

Reality: INT columns only store whole numbers; decimals are lost or cause errors.

Why it matters: Using INT for decimals causes data loss and incorrect calculations.

Quick: Is it okay to store dates as VARCHAR text? Commit to yes or no.

Common Belief: Storing dates as text (VARCHAR) works fine as long as format is consistent.

Reality: Storing dates as text breaks date functions like comparisons and calculations.

Why it matters: Date operations fail or give wrong results, making reports unreliable.

Quick: Does DECIMAL always use less space than FLOAT? Commit to your answer.

Common Belief: DECIMAL uses less space than floating-point types for decimals.

Reality: DECIMAL often uses more space because it stores exact decimals, unlike FLOAT which stores approximate values.

Why it matters: Choosing DECIMAL without knowing space cost can lead to larger databases and slower queries.

Expert Zone

1

Some databases optimize storage for small INT ranges using smaller byte sizes (e.g., TINYINT), which can save space but require careful type choice.

2

VARCHAR columns with large maximum lengths can degrade performance even if actual data is short, due to internal storage and indexing overhead.

3

DATE types internally count days from a fixed point, enabling fast date arithmetic but requiring understanding of calendar quirks like leap years.

When NOT to use

Avoid using VARCHAR for numeric or date data; use INT, DECIMAL, or DATE instead. For approximate decimals where precision is less critical, FLOAT or DOUBLE may be better. When storing large text, use TEXT or CLOB types instead of VARCHAR. For very large numbers, consider BIGINT or specialized numeric types.

Production Patterns

In production, INT is commonly used for IDs and counts, VARCHAR for names and descriptions, DATE for timestamps and events, and DECIMAL for money and precise measurements. Indexes are often built on INT columns for speed. Data types are chosen carefully to balance precision, storage, and query performance.

Connections

Data Validation

Data types enforce rules that validate data correctness.

Understanding data types helps grasp how databases prevent invalid data entry, similar to form input validation in apps.

Memory Management in Programming

Data types in databases relate to how programming languages allocate memory for variables.

Knowing how types affect storage in programming clarifies why databases use fixed or variable storage for different data types.

Accounting Principles

DECIMAL data type's precision is crucial for financial calculations in accounting.

Understanding DECIMAL's exactness connects database design to real-world needs for accurate money handling.

Common Pitfalls

#1 Storing numbers as text in VARCHAR columns.

Wrong approach:

CREATE TABLE sales (amount VARCHAR(10));
INSERT INTO sales VALUES ('100'), ('20'), ('3');
SELECT * FROM sales ORDER BY amount;

Correct approach:

CREATE TABLE sales (amount INT);
INSERT INTO sales VALUES (100), (20), (3);
SELECT * FROM sales ORDER BY amount;

Root cause:

Misunderstanding that VARCHAR stores text and sorts alphabetically, causing numeric sorting errors.

#2 Using INT to store decimal values.

Wrong approach:

CREATE TABLE products (price INT);
INSERT INTO products VALUES (19.99);

Correct approach:

CREATE TABLE products (price DECIMAL(5,2));
INSERT INTO products VALUES (19.99);

Root cause:

Not knowing INT only stores whole numbers, leading to data truncation or errors.

#3 Storing dates as VARCHAR strings.

Wrong approach:

CREATE TABLE events (event_date VARCHAR(10));
INSERT INTO events VALUES ('2024-06-01');
SELECT * FROM events WHERE event_date > '2024-01-01';

Correct approach:

CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-06-01');
SELECT * FROM events WHERE event_date > '2024-01-01';

Root cause:

Believing text format is enough for dates, ignoring date functions and comparisons.

Key Takeaways

Column data types tell the database what kind of data each column holds, guiding storage and operations.

Choosing the right data type prevents errors, saves space, and improves query speed.

INT stores whole numbers, VARCHAR stores text, DATE stores calendar dates, and DECIMAL stores precise decimal numbers.

Using wrong data types causes bugs like incorrect sorting, data loss, or failed calculations.

Understanding data types deeply helps design reliable, efficient databases for real-world applications.