Data types define the type of data that can be stored in a column of a table. They are crucial for database design as they help ensure data integrity and optimize storage and performance. Different SQL databases support various data types, but most share common categories.
Table of Contents
Categories of Data Types
Numeric Data Types
Used for storing numeric values, including integers and floating-point numbers.
| Data Type | Description | Example |
| INT | A standard integer type. | id INT |
| SMALLINT | A smaller range integer type. | age SMALLINT |
| BIGINT | A larger range integer type. | population BIGINT |
| FLOAT | A floating-point number. | price FLOAT |
| DOUBLE | A double-precision floating-point number. | weight DOUBLE |
| DECIMAL(p,s) | A fixed-point number with p total digits and s decimal places. | salary DECIMAL(10,2) |
Note: The above data types are consistent across MySQL, PostgreSQL, and MSSQL.
String Data Types
Used for storing text data, including characters and variable-length strings.
| Data Type | Description | Example |
| CHAR(n) | Fixed-length string, padded with spaces if necessary. | code CHAR(5) |
| VARCHAR(n) | Variable-length string, up to n characters. | name VARCHAR(100) |
| TEXT | Variable-length string with a maximum length of 65,535 bytes (in MySQL). | description TEXT |
| NCHAR(n) | Fixed-length Unicode string. | unicode_char NCHAR(10) |
| NVARCHAR(n) | Variable-length Unicode string. | unicode_name NVARCHAR(100) |
| CLOB | Character large object for storing large text data (in PostgreSQL and MSSQL). | bio CLOB |
Note: TEXT and CLOB types may differ in maximum size limits across databases.
Date and Time Data Types
Used for storing dates, times, and timestamps.
| Data Type | Description | Example |
| DATE | Stores date values (year, month, day). | birthdate DATE |
| TIME | Stores time values (hour, minute, second). | event_time TIME |
| DATETIME | Combines date and time values. | created_at DATETIME |
| TIMESTAMP | Stores timestamp (date and time), auto-updating for changes. | last_updated TIMESTAMP |
| INTERVAL | Represents a time span (in PostgreSQL). | duration INTERVAL |
Note: The TIMESTAMP type may auto-update on certain actions in some databases.
Boolean Data Type
Used for storing truth values.
| Data Type | Description | Example |
| BOOLEAN | Stores TRUE, FALSE, or NULL. | is_active BOOLEAN |
Note: Supported consistently across MySQL, PostgreSQL, and MSSQL.
Binary Data Types
Used for storing binary data, such as images and files.
| Data Type | Description | Example |
| BINARY(n) | Fixed-length binary data. | image BINARY(100) |
| VARBINARY(n) | Variable-length binary data. | file VARBINARY(500) |
| BLOB | Binary large object for storing large binary data (in MySQL). | file BLOB |
Note: The BLOB type is specific to MySQL, while PostgreSQL uses BYTEA.
Creating a Table with Various Data Types
Here’s an example of how to create a table using various data types in SQL:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE,
is_active BOOLEAN,
profile_picture BLOB
);Conclusion
Choosing the right data types in SQL is essential for database design, impacting both performance and data integrity. By understanding the available data types—numeric, string, date and time, boolean, and binary—database administrators can optimize their databases for efficient storage and retrieval.
Most Commented