SQL Data Types
- By utilizing various methods, SQL can determine the data type that can be stored in each column of a database table.
- Choosing the correct data type can have significant implications for protecting against data loss, optimizing storage usage, and improving query performance.
- Contains examples and explanations of all SQL data types and their respective functions.
What are SQL Data Types?
- A relational database can store data types for a column, which are determined by SQL data type specifications.
- By acting as constraints, they ensure that only valid data is entered into a table.
- As an example, a column that stores ages for text values is not required.
- The broadest range of SQL data types includes numeric, string (singularly optional), date/time, boolean, and so on.
- Let’s examine each category in depth.
1. Numeric Data Types
- Numerical data types are represented by integers and decimals.
- The use of these is suitable for calculating mathematical concepts and data that requires numerical analysis.
| Data Type | Description | Example |
| INT | Stores whole numbers. | age INT → 25, 30, 100 |
| SMALLINT | Stores smaller whole numbers. | votes SMALLINT → 1500 |
| BIGINT | Stores large whole numbers. | population BIGINT → 9876543210 |
| DECIMAL | Stores fixed-point numbers with precision. | salary DECIMAL(10,2) → 50000.75 |
| FLOAT | Stores approximate floating-point numbers. | rating FLOAT → 4.5 |
| BIT | Stores binary values (0 or 1). | active BIT → 1 (true) |
- Example Query:
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(8, 2),
stock SMALLINT
);
INSERT INTO products (product_id, price, stock)
VALUES (1, 1999.99, 50);
2. Character/String Data Types
- String data types contain binary data, text, or alphanumeric characters.
- It is used for other textual information, names and descriptions.
| Data Type | Description | Example |
| CHAR(n) | Fixed-length string of n characters. | code CHAR(5) → ‘A1234’ |
| VARCHAR(n) | Variable-length string up to n characters. | name VARCHAR(50) → ‘John Doe’ |
| TEXT | Stores large blocks of text. | bio TEXT → ‘A detailed biography’ |
| BLOB | Stores binary data (e.g., images, files). | image BLOB → JPEG/PNG data |
- Example Query:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
bio TEXT
);
INSERT INTO employees (employee_id, name, bio)
VALUES (1, 'Jane Smith', 'A highly skilled Java developer.');
3. Date and Time Data Types
- When dealing with temporal data it is important to store the dates, times and timestamps.
| Data Type | Description | Example |
| DATE | Stores dates in YYYY-MM-DD format. | hire_date DATE → ‘2024-12-15’ |
| TIME | Stores time in HH:MM:SS format. | meeting_time TIME → ’14:30:00′ |
| DATETIME | Stores date and time. | event DATETIME → ‘2024-12-17 10:00:00’ |
| TIMESTAMP | Similar to DATETIME, with timezone support. | login TIMESTAMP → ‘2024-12-17 08:00:00+00:00’ |
| YEAR | Stores a year in four digits. | launch_year YEAR → 2024 |
- Example Query:
CREATE TABLE events (
event_id INT PRIMARY KEY,
event_name VARCHAR(50),
event_date DATE,
start_time TIME
);
INSERT INTO events (event_id, event_name, event_date, start_time)
VALUES (101, 'SQL Conference', '2025-01-15', '09:00:00');
4. Boolean Data Type
- The storage type of SQL is BOOLEAN, which stores logical values (TRUE or FALSE).
- While some SQL databases, such as MySQL’s, use TINYINT to represent boolean values, modern SQL database systems directly accept the type BOOLEAN.
| Data Type | Description | Example |
| BOOLEAN | Stores true/false values. | is_active BOOLEAN → TRUE |
- Example Query:
CREATE TABLE tasks (
task_id INT PRIMARY KEY,
task_name VARCHAR(100),
is_completed BOOLEAN
);
INSERT INTO tasks (task_id, task_name, is_completed)
VALUES (1, 'Learn SQL Basics', TRUE);
5. Spatial Data Types
- The storage of spatial data, which includes points, lines, polygons, and other geographical data types, is done through different data type schemes.
- They are commonly used in Geographic Information Systems (GIS).
| Data Type | Description | Example |
| POINT | Stores a single geographical point. | location POINT → (40.7128, -74.0060) |
| GEOMETRY | Stores geometric shapes. | shape GEOMETRY → Polygon |
| GEOGRAPHY | Stores global location data. | coordinates GEOGRAPHY → Latitude/Longitude |
- Example Query:
CREATE TABLE stores (
store_id INT PRIMARY KEY,
store_name VARCHAR(50),
location POINT
);
INSERT INTO stores (store_id, store_name, location)
VALUES (1, 'Tech Store', POINT(40.7128, -74.0060));
6. JSON and XML Data Types
- The popularity of these types in modern web applications has allowed for the storage of semi-structured data, such as JSON documents or XML files.
| Data Type | Description | Example |
| JSON | Stores JSON-formatted data | data JSON → ‘{“name”:”John”}’ |
| XML | Stores XML-formatted data. | data XML → ‘<name>John</name>’ |
- Example Query:
CREATE TABLE api_logs (
log_id INT PRIMARY KEY,
response_data JSON
);
INSERT INTO api_logs (log_id, response_data)
VALUES (1, '{"status": "success", "code": 200}');
Why choosing the right data type matters?
- Anti-Invalid Data entries are avoided through Data Integrity.
- Storage Optimization involves the utilization of appropriate storage types to minimize space usage.
- Effective: Improves query speed by utilizing efficient data types.