SQL Data Types

When creating a table in SQL, every column must be assigned a data type. A data type tells the database what kind of value a column will store — whether it is a number, a piece of text, a date, or something else.

Think of it like labeling compartments in a drawer. One compartment is labeled "only pens," another "only coins," and another "only paper." SQL data types work the same way — they make sure only the right kind of data goes into each column.

Why Data Types Matter

• They ensure data accuracy — a phone number column will not accidentally store someone's name.
• They optimize storage — using the right type helps the database use memory efficiently.
• They help with calculations — only numeric types can be used in math operations like addition or average.

Main Categories of SQL Data Types

SQL data types are grouped into three main categories: String (Text), Numeric, and Date and Time.

1. String (Text) Data Types

String data types store text values — like names, addresses, descriptions, and email IDs.

When to Use CHAR vs VARCHAR

Use CHAR when the value will always be the same length — for example, a country code like 'IN' or 'US'. Use VARCHAR when the length of the value can vary — for example, a person's name or a city name.

-- Example
StudentName VARCHAR(50),
CountryCode CHAR(2)

2. Numeric Data Types

Numeric data types store numbers — both whole numbers and decimal numbers.

Example: Using Numeric Types

-- Storing age as INT and price as DECIMAL
Age INT,
Price DECIMAL(8, 2)

A DECIMAL(8, 2) column can store numbers like 12345.99 — up to 8 total digits with exactly 2 after the decimal point. This is commonly used for prices and salaries.

3. Date and Time Data Types

These data types store dates and times — like order dates, birth dates, or appointment times.

Example: Using Date Types

-- Storing birth date and enrollment date
BirthDate DATE,
EnrollmentDate DATETIME

4. Boolean Data Type

The BOOLEAN (or BOOL) data type stores only two values: TRUE or FALSE. In many databases like MySQL, it is stored internally as 1 (TRUE) or 0 (FALSE).

-- Example
IsActive BOOLEAN

This can be used to track whether a student is currently enrolled, whether an account is active, or whether an order has been delivered.

Complete Table Definition Example

Here is an example showing a table that uses multiple data types together:

CREATE TABLE Students (
    StudentID   INT,
    StudentName VARCHAR(50),
    Age         INT,
    Fees        DECIMAL(8, 2),
    City        CHAR(30),
    JoinDate    DATE,
    IsActive    BOOLEAN
);

What Each Column Stores

Key Points to Remember

• Always choose the smallest data type that fits the data — this saves storage space.
• Use VARCHAR instead of CHAR for text that varies in length.
• Use DECIMAL instead of FLOAT when exact values matter, like money.
• Date values in SQL are always written in YYYY-MM-DD format.
• Choosing the wrong data type can cause errors during data insertion or lead to inaccurate query results.

Summary

SQL data types define what kind of data each column in a table can hold. The three main groups are string types (for text), numeric types (for numbers), and date/time types (for dates and times). Choosing the correct data type for each column is a critical step in building a well-structured and efficient database.