Data Types in SQL define the kind of information that can be stored in a database column. Choosing the correct SQL data type helps improve storage efficiency, data accuracy, and query performance. Common SQL data types include numeric, character, date and time, and binary types. Understanding these data types is essential for designing reliable databases.
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.
Frequently Asked Questions
Data types in SQL define the type of values that can be stored in a database column. They ensure data is stored correctly and help maintain data integrity. Common examples include INT, VARCHAR, DATE, and FLOAT.
Choosing the correct SQL data type improves database performance, reduces storage requirements, and prevents invalid data from being stored in tables.
VARCHAR is one of the most commonly used SQL data types because it stores variable-length text data efficiently. Other frequently used types include INT for numbers and DATE for date values.
CHAR stores fixed-length strings and always uses the specified amount of storage. VARCHAR stores variable-length strings and uses only the space needed for the actual data, making it more storage-efficient.
Most Commented