SQL

SQL Data Types

🗂️ SQL Data Types define the kind of data that can be stored in a table column. Choosing the correct data type helps maintain data integrity, improves storage efficiency, and enhances query performance. Every column in a database table must have a data type that determines the values it can store.

📖 What are SQL Data Types?

A data type specifies the type, size, and format of data that a column accepts. For example, a student's age should be stored as a numeric value, while a student's name should be stored as text. Selecting appropriate data types ensures that only valid data is stored in the database.

Information

Although most relational database systems support common SQL data types, some databases provide additional vendor-specific data types or slight variations in their implementation.

🗂️ Categories of SQL Data Types

SQL data types are generally divided into the following categories:

🔢 Numeric Data Types
🔤 Character (String) Data Types
📅 Date and Time Data Types
✅ Boolean Data Types
📦 Binary Data Types

🔢 Numeric Data Types

Numeric data types store integers, decimal numbers, and floating-point values.

Data Type	Description	Example Value
INT	Stores whole numbers.	100
SMALLINT	Stores smaller integer values.	250
BIGINT	Stores very large integers.	9876543210
DECIMAL(p,s)	Stores exact decimal values.	1999.95
NUMERIC(p,s)	Stores exact numeric values.	12345.67
FLOAT	Stores approximate floating-point numbers.	3.14159
REAL	Stores single-precision floating-point values.	98.75

Example: Numeric Data Types

Creating Numeric Columns

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2),
    Stock INT
);

🔤 Character (String) Data Types

Character data types store letters, numbers, symbols, and other text values.

Data Type	Description	Example
CHAR(n)	Stores fixed-length text.	AB123
VARCHAR(n)	Stores variable-length text.	John Smith
TEXT	Stores long text.	Article content...

Example: Character Data Types

Creating Character Columns

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Country CHAR(2)
);

Tip

Use VARCHAR when text length varies. Reserve CHAR for fixed-length values such as country codes or status codes.

📅 Date and Time Data Types

Date and time data types store dates, times, or both.

Data Type	Description	Example
DATE	Stores only the date.	2026-07-01
TIME	Stores only the time.	14:30:00
DATETIME	Stores both date and time.	2026-07-01 14:30:00
TIMESTAMP	Stores a timestamp value.	2026-07-01 14:30:00

Example: Date and Time Data Types

Creating Date Columns

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    OrderTime TIME,
    CreatedAt TIMESTAMP
);

✅ Boolean Data Type

A Boolean data type stores logical values representing two possible states, typically TRUE or FALSE. Some database systems internally store Boolean values as 1 and 0.

Boolean Example

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    IsActive BOOLEAN
);

📦 Binary Data Types

Binary data types store raw binary data such as images, audio files, documents, and other multimedia content.

Data Type	Description
BINARY	Stores fixed-length binary data.
VARBINARY	Stores variable-length binary data.
BLOB	Stores large binary objects such as images and videos.

📊 Common SQL Data Types Summary

Category	Common Data Types	Typical Usage
Numeric	INT, DECIMAL, FLOAT	Numbers, prices, quantities
Character	CHAR, VARCHAR, TEXT	Names, addresses, descriptions
Date & Time	DATE, TIME, DATETIME, TIMESTAMP	Dates, schedules, timestamps
Boolean	BOOLEAN	True/False values
Binary	BINARY, VARBINARY, BLOB	Files, images, multimedia

💼 Real-World Example

Consider an Employee table:

Employee Table with Different Data Types

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Salary DECIMAL(10,2),
    DateOfJoining DATE,
    IsPermanent BOOLEAN,
    ProfilePhoto BLOB
);

In this example:

EmployeeID stores a unique integer for each employee.
FullName stores variable-length text.
Salary stores exact monetary values.
DateOfJoining stores the joining date.
IsPermanent stores a logical value.
ProfilePhoto stores binary image data.

⚠️ Choosing the Right Data Type

Best Practice

Choose the smallest data type that satisfies your requirements. This improves storage efficiency, reduces memory usage, enhances query performance, and helps maintain data accuracy. Avoid using large text or numeric types unless they are genuinely needed.

🚀 Key Points to Remember

📌 Every table column must have a data type.
📌 Numeric data types store numbers.
📌 Character data types store text.
📌 Date and time data types store temporal values.
📌 Boolean data types store logical values.
📌 Binary data types store files and multimedia.
📌 Selecting the appropriate data type improves database performance and reliability.

>>"Choosing the right data type is the foundation of a well-designed database."

Summary

✅ SQL data types define the type of information that each column can store. Understanding numeric, character, date and time, Boolean, and binary data types enables you to design efficient, reliable, and scalable database tables.