Day 26: Data Types In SQL

Day 26 Of Learning

Image By Author

Hey Guys, hope you’re good.

Today, we’ll be looking at data types in SQL. We’ll also be discussing common data types as well as how much memory each data type takes.

So, let’s get started.

What Are Data Types?

A data type defines the kind of value that a column or variable can store. Data types ensure data integrity, optimize storage, and improve query performance by allocating the appropriate amount of memory for each value. Choosing the right data type is crucial in database design to prevent errors, reduce storage space, and enhance query efficiency.

Common Data Types In SQL

SQL provides a variety of data types, which can be broadly categorized into the following groups:

1. Numeric Data Types

Numeric data types store numerical values, which can be either integers or floating-point numbers.

• INT (INTEGER): Stores whole numbers without decimals. Example: 123

• SMALLINT: A smaller integer type that takes up less space. Example: 32000

• BIGINT: Stores very large integer values. Example: 9223372036854775807

• DECIMAL (NUMERIC): Stores fixed-point numbers with precise decimal places. Example: 10.75

• FLOAT & DOUBLE: Stores floating-point numbers for approximate calculations. Example: 3.14159

2. Character (String) Data Types

String data types store text and alphanumeric characters.

• CHAR(n): Fixed-length character storage. Example: CHAR(10) stores exactly 10 characters.

• VARCHAR(n): Variable-length character storage. Example: VARCHAR(50) stores up to 50 characters but uses space only for the actual input.

• TEXT: Used for large text data, such as articles or comments.

3. Date and Time Data Types

These data types store date and time values.

• DATE: Stores a date (YYYY-MM-DD). Example: 2025-03-13

• TIME: Stores a time (HH:MI:SS). Example: 14:30:00

• DATETIME: Stores both date and time. Example: 2025-03-13 14:30:00

• TIMESTAMP: Similar to DATETIME but optimized for tracking changes.

4. Boolean Data Type

• BOOLEAN: Stores TRUE or FALSE (represented as 1 and 0 in some databases).

5. Binary Data Types

These data types store binary data such as images and files.

• BLOB (Binary Large Object): Used for storing large binary data such as images and videos.

• VARBINARY(n): Variable-length binary data storage.

These are some common data types, however the most common that I have worked with are, BOOLEAN, Numeric, Character, and Date and Time data types. These will vary from project to project.

How Much Memory Does Each Data Type Take?

Understanding memory allocation helps optimize database performance. Below is an approximate memory usage guide:

Numeric Data Types:

• TINYINT: 1 byte

• SMALLINT: 2 bytes

• MEDIUMINT: 3 bytes

• INT: 4 bytes

• BIGINT: 8 bytes

• DECIMAL(m, d): Varies, but roughly (m+2) bytes

• FLOAT: 4 bytes

• DOUBLE: 8 bytes

Character Data Types:

• CHAR(n): Fixed n bytes

• VARCHAR(n): Variable, up to n+1 bytes

• TEXT: Variable, up to 2GB (depending on SQL implementation)

Date and Time Data Types:

• DATE: 3 bytes

• TIME: 3 bytes

• DATETIME: 8 bytes

• TIMESTAMP: 4 bytes

Boolean Data Type:

• BOOLEAN: 1 byte (stored as TINYINT in some databases)

Binary Data Types:

• BLOB: Variable, depending on size

• VARBINARY(n): Variable, up to n+1 bytes

Understanding the memory allocation is crucial to the right use of the data types as it helps optimize your queries and takes less processing time.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)