Basic Data Types in SQL

Table of Contents

1. Introduction
2. Why Data Types Matter in SQL
3. Overview of SQL Data Type Categories
4. Integer Types
5. Decimal and Floating-Point Types
6. Character and String Types
7. Date and Time Types
8. Boolean Type
9. Binary and Blob Types
10. ENUM and SET (MySQL Specific)
11. Serial and Auto-Incrementing Types
12. Choosing the Right Data Type
13. Data Type Compatibility Across Databases
14. Implicit and Explicit Type Casting
15. NULL Values and Data Types
16. Performance Implications of Data Types
17. Data Type Errors and Troubleshooting
18. Best Practices for Using Data Types
19. Real-World Schema Example
20. Summary and Next Steps

1. Introduction

In SQL, every column in a table must be assigned a data type. Data types determine what kind of data can be stored in each column — numbers, text, dates, boolean values, and more. Choosing the right data type is essential for data integrity, performance, and storage efficiency.

2. Why Data Types Matter in SQL

• Validation: Prevents incorrect data from being stored
• Storage Optimization: Saves space with efficient types
• Performance: Speeds up indexing and searching
• Data Integrity: Helps avoid logic and arithmetic errors
• Compatibility: Ensures consistency when exporting/importing data

3. Overview of SQL Data Type Categories

4. Integer Types

Used to store whole numbers.

Example:

age INT;
population BIGINT;

5. Decimal and Floating-Point Types

Used for storing numbers with decimals.

• DECIMAL(p, s): Precise fixed-point values.
• FLOAT/REAL/DOUBLE: Approximate values, prone to rounding errors.

Example:

price DECIMAL(10, 2);  -- 99999999.99
weight FLOAT;

Use DECIMAL for financial calculations; FLOAT for scientific or approximate values.

6. Character and String Types

Example:

username VARCHAR(50);
postal_code CHAR(6);
bio TEXT;

Avoid using TEXT unless necessary — it lacks indexing in some systems.

7. Date and Time Types

Example:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
birthday DATE;

PostgreSQL supports more options like INTERVAL, TIME WITH TIME ZONE.

8. Boolean Type

Stores true or false values.

is_active BOOLEAN;

• In MySQL: stored as TINYINT(1) behind the scenes
• In PostgreSQL: true BOOLEAN type

9. Binary and Blob Types

Used to store binary data like images, PDFs, or files.

Avoid using binary types for large media — use external storage + reference.

10. ENUM and SET (MySQL Specific)

ENUM

status ENUM('active', 'inactive', 'pending');

SET

tags SET('blog', 'news', 'video');

They restrict allowed values and save space but reduce portability.

11. Serial and Auto-Incrementing Types

Used for primary key columns that auto-increment:

SQLite:

id INTEGER PRIMARY KEY AUTOINCREMENT

MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id SERIAL PRIMARY KEY

12. Choosing the Right Data Type

• For IDs: use INT or BIGINT with AUTO_INCREMENT or SERIAL
• For names/emails: VARCHAR(100)
• For monetary values: DECIMAL(10, 2)
• For status flags: BOOLEAN or ENUM
• For timestamps: TIMESTAMP

Avoid over-allocating space — use just enough to fit expected data.

13. Data Type Compatibility Across Databases

SQLite uses dynamic typing, so types are more relaxed.

14. Implicit and Explicit Type Casting

SQL can convert data types:

SELECT '123' + 1; -- Implicit cast to integer

Use CAST() or :: (PostgreSQL):

SELECT CAST('2024-01-01' AS DATE);
SELECT '10'::INT;  -- PostgreSQL

15. NULL Values and Data Types

NULL means the value is unknown or not applicable.

age INT NULL;

Be cautious: NULL != 0 and NULL != ''

Always use IS NULL or IS NOT NULL when filtering.

16. Performance Implications of Data Types

• Smaller types = faster performance
• Indexes on large TEXT or BLOB fields are costly
• Use appropriate precision in numbers (avoid FLOAT if DECIMAL is safer)

17. Data Type Errors and Troubleshooting

Common errors include:

• Type mismatch (e.g., inserting text into INT)
• Truncation (e.g., inserting long strings into VARCHAR(10))
• Implicit conversions breaking logic

Solution: always validate types and apply constraints carefully.

18. Best Practices for Using Data Types

• Be explicit with data types — avoid relying on defaults
• Always constrain string lengths with VARCHAR(n)
• Use DECIMAL for money, never FLOAT
• Don’t use TEXT unless necessary
• Use BOOLEAN for true/false flags, not strings like ‘yes’/’no’

19. Real-World Schema Example

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    is_available BOOLEAN DEFAULT TRUE,
    added_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This schema combines most of the common data types used in real projects.

20. Summary and Next Steps

Understanding SQL data types is foundational to writing clean, optimized, and scalable queries. By assigning the right type to each column, you gain better control over your application’s data.