SQL data types define the kind of value that can be stored in a database column. Choosing the appropriate data type helps store data correctly, use storage efficiently, and perform suitable operations on that data.
A data type specifies what kind of data a column can store.
For example:
name VARCHAR(100) age INT fee DECIMAL(10,2) admission_date DATE
Data types help a database understand how values should be stored and processed.
Using appropriate data types can help with:
SQL data types can broadly be grouped into categories such as:
The exact list depends on the database management system.
INT is commonly used to store whole numbers without a fractional part.
Examples:
age INT quantity INT student_id INT
Example values:
18 25 100 500
Some database systems provide multiple integer types with different storage sizes and ranges.
For example, MySQL provides:
The appropriate type depends on the range of numbers your application needs to store.
BIGINT is used for whole numbers that may exceed the range appropriate for a regular INT column.
It can be useful for very large identifiers or counters.
order_id BIGINT
The exact range depends on the database system and whether the type is signed or unsigned.
DECIMAL is commonly used for exact numeric values, especially amounts where exact decimal precision matters.
Example:
price DECIMAL(10,2)
Here, 10 represents the precision and 2 represents the scale in systems such as MySQL.
Example values:
250.00 9999.50 12500.75
FLOAT is a floating-point numeric data type supported by many database systems.
It is useful when approximate numeric representation is acceptable.
temperature FLOAT
DOUBLE is another floating-point numeric type available in several database systems, including MySQL.
It can store approximate numeric values with a larger range or precision than some single-precision floating-point types.
measurement DOUBLE
CHAR is used to store fixed-length character strings.
gender CHAR(1)
For example, a column may store values such as:
M F
CHAR can be useful when values have a consistent length.
VARCHAR is used to store variable-length character strings.
name VARCHAR(100) email VARCHAR(150) city VARCHAR(100)
For example, a name column can contain text values of different lengths.
VARCHAR is commonly used for names, email addresses, cities, titles, and similar text fields.
| CHAR | VARCHAR |
|---|---|
| Fixed-length character type | Variable-length character type |
| Useful for values with consistent length | Useful for values with varying length |
| Example: CHAR(1) | Example: VARCHAR(100) |
| Often used for short fixed-format values | Commonly used for names and other text |
TEXT is used in systems such as MySQL for storing variable-length text.
It can be useful for content such as:
description TEXT
Different database systems provide different text types and size limits.
DATE is used to store calendar dates.
admission_date DATE
Example:
2026-09-20
A DATE value does not represent a time of day.
TIME is used to represent a time of day or time interval according to the database system.
class_time TIME
Example:
09:30:00
DATETIME stores both date and time in systems that support this type, such as MySQL.
created_at DATETIME
Example:
2026-09-20 10:30:00
It is useful for timestamps such as record creation or update times.
TIMESTAMP is a date-and-time type provided by several database systems. Its exact behavior differs between systems.
In MySQL, TIMESTAMP can be useful for tracking date and time values and can support automatic timestamp behavior in suitable table definitions.
created_at TIMESTAMP
Some database systems provide a YEAR data type. MySQL supports YEAR for storing year values.
passing_year YEAR
Example:
2026
A Boolean value represents a logical state such as true or false.
Database systems handle Boolean types differently. For example, MySQL supports BOOLEAN as a synonym for TINYINT(1).
is_active BOOLEAN
An application may use such a column to represent whether a record is active.
Binary data types are used to store binary values rather than ordinary text.
Depending on the database system, binary types can be used for data such as:
The exact binary data types vary between database systems.
BLOB stands for Binary Large Object. In systems such as MySQL, BLOB types can store binary data such as images or other files.
Different BLOB variants support different maximum sizes.
profile_image BLOB
Some database systems provide an ENUM type. MySQL supports ENUM for columns whose values must come from a predefined list.
Example:
status ENUM('Pending', 'Paid', 'Cancelled')
This can be useful for a small, fixed set of allowed values, although the best design depends on the application and database system.
Modern relational database systems may provide a JSON data type for storing JSON documents.
For example, MySQL and PostgreSQL provide JSON-related functionality.
preferences JSON
JSON can be useful when data has a flexible or nested structure, although normal relational columns are often preferable for data that needs frequent relational querying.
Choose a data type according to the kind of data the column needs to store.
| Data | Possible Type |
|---|---|
| Student ID | INT |
| Name | VARCHAR |
| Description | TEXT |
| Age | INT |
| Fee | DECIMAL |
| Admission Date | DATE |
| Created Date and Time | DATETIME / TIMESTAMP |
| Active Status | BOOLEAN or database-specific equivalent |
Here is an example of a student table using different data types:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
fee DECIMAL(10,2),
admission_date DATE,
is_active BOOLEAN
);
Each column uses a data type appropriate for the kind of value it is expected to store.
DECIMAL is often written using precision and scale.
DECIMAL(10,2)
Here:
For example, values such as 1250.50 can be represented with this definition.
VARCHAR can be defined with a maximum length.
name VARCHAR(100)
This definition specifies a maximum length of 100 characters according to the database system's rules.
The appropriate length should be chosen based on the expected data.
Sometimes a value needs to be converted from one data type to another. SQL systems provide conversion or casting functions for this purpose.
For example, in MySQL:
SELECT CAST('100' AS UNSIGNED);
The exact syntax for type conversion differs between database systems.
Beginners commonly make mistakes such as:
The following table demonstrates several commonly used SQL data types.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
salary DECIMAL(10,2),
joining_date DATE,
joining_time TIME,
created_at DATETIME,
is_active BOOLEAN
);
The exact behavior of some types, especially BOOLEAN, DATETIME, and auto-generated timestamp features, depends on the database system.
Question: Which SQL data type is commonly preferred for storing exact monetary values such as fees and prices?