🧾 SQL Data Types: INT, VARCHAR, DATE, BOOLEAN, and More Explained

When working with databases, it’s crucial to store data in the right format. This is where data types in SQL come in. Every column in a SQL table must have a defined data type so that the database knows how to store, validate, and retrieve values.

In this guide, we’ll break down the most commonly used data types in SQL — such as INT, VARCHAR, DATE, and BOOLEAN — with practical examples and use cases. Whether you’re a student, a developer, or just getting started with SQL, this article is for you.

📦 What Are SQL Data Types?

SQL data types define the type of data that can be stored in a column of a table. Choosing the correct data type:

• Ensures data integrity
• Saves storage space
• Improves query performance

Each SQL database (like MySQL, PostgreSQL, or SQL Server) might have slight variations, but the core data types are mostly consistent across all platforms.

📋 Common Categories of SQL Data Types

SQL data types can be grouped into several broad categories:

1. Numeric Data Types
2. Character/String Data Types
3. Date and Time Data Types
4. Boolean Data Type
5. Other/Miscellaneous Types (e.g., BLOB, JSON, ENUM)

Let’s look at each of them in detail.

🔢 1. Numeric Data Types

Used to store numbers — both integers and floating points.

✅ INT (Integer)

• Stores whole numbers (positive or negative)
• Example: 1, -10, 200

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price INT
);

✅ SMALLINT, BIGINT

• SMALLINT uses less storage (typically 2 bytes)
• BIGINT is for very large numbers (up to 8 bytes)

✅ DECIMAL(p, s) or NUMERIC(p, s)

• Used for fixed-point numbers (perfect for financial data)
• p = precision (total digits), s = scale (digits after decimal)

Salary DECIMAL(10, 2)  -- Max 10 digits, 2 after the decimal

✅ FLOAT and REAL

• Used for floating-point (approximate) numbers
• Less precise than DECIMAL

Note: Use DECIMAL for exact calculations (e.g., money), and FLOAT when approximation is acceptable (e.g., scientific data).

📝 2. Character/String Data Types

Used to store text, characters, and strings.

✅ VARCHAR(n)

• Variable-length character string with a max length n
• Efficient for most text fields like names, emails

Name VARCHAR(100),
Email VARCHAR(150)

✅ CHAR(n)

• Fixed-length string (always reserves n characters)
• Faster but less flexible than VARCHAR

Code CHAR(3)  -- Stores exactly 3 characters like "USA"

✅ TEXT

• Used for long text like comments, descriptions
• Cannot be indexed or sorted easily in some databases

Description TEXT

📅 3. Date and Time Data Types

Used for storing dates, times, or both.

✅ DATE

• Stores calendar dates in YYYY-MM-DD format

JoinDate DATE

✅ TIME

• Stores time of day in HH:MM:SS format

LoginTime TIME

✅ DATETIME or TIMESTAMP

• Stores both date and time (e.g., 2025-04-07 14:30:00)
• TIMESTAMP can auto-update in some systems

CreatedAt DATETIME

✅ 4. Boolean Data Type

Used to store true or false values.

✅ BOOLEAN or BOOL

• Stores TRUE or FALSE (some systems use 1 and 0)
• Great for status fields, flags, toggles

IsActive BOOLEAN

Note: Some SQL engines like MySQL store BOOLEAN as a tiny integer (TINYINT(1)).

🧩 5. Other Useful Data Types

✅ ENUM

• A list of predefined values. Great for fixed options like status, gender, etc.

Status ENUM('Pending', 'Shipped', 'Cancelled')

✅ BLOB (Binary Large Object)

• Stores binary data like images, videos, or files

ProfilePic BLOB

✅ JSON

• Stores structured JSON data (supported in PostgreSQL, MySQL 5.7+)

Details JSON

📊 Choosing the Right Data Type: Best Practices

❌ Common Mistakes to Avoid

• Using TEXT when VARCHAR is enough – wastes space
• Using FLOAT for money – can cause rounding errors
• Choosing CHAR instead of VARCHAR unnecessarily
• Forgetting to define max length in VARCHAR(n)
• Ignoring time zone differences in DATETIME or TIMESTAMP

📚 Real-World Example: User Table

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    JoinDate DATE DEFAULT CURRENT_DATE,
    IsVerified BOOLEAN DEFAULT FALSE,
    Bio TEXT
);

This table uses a variety of data types for:

• Identifiers (INT)
• Text fields (VARCHAR, TEXT)
• Dates (DATE)
• Flags (BOOLEAN)

🧠 Quick Recap Table

🏁 Conclusion

SQL data types are more than just labels — they define how your data behaves, how much space it takes, and how efficiently it can be queried. Whether you’re building a simple app or a massive enterprise system, choosing the right data types ensures performance, reliability, and data integrity.

Always keep in mind the nature of your data, how you’ll use it, and what constraints it requires. As your skills grow, mastering data types will help you become a confident and effective database designer.