SQL Data Types: Syntax, Usage, and Examples

SQL data types define the kind of data that each column in a table can store. They ensure consistency, optimize storage, and improve query performance. Choosing the right data type is essential for efficient database design, data integrity, and smooth application performance.

How to Use SQL Data Types

Each column in a SQL table requires a specific data type, which determines the format and constraints for the data stored in that column. The correct choice of a data type helps prevent errors, ensures efficient storage, and improves query execution speed.

Common SQL Data Types

SQL provides several categories of data types for handling different kinds of data:

• Numeric Data Types:
  • INT, SMALLINT, BIGINT: Used for whole numbers.
  • DECIMAL, NUMERIC: Used for precise decimal values, often in financial applications.
  • FLOAT, REAL: Used for floating-point numbers but may introduce precision errors.
• String and Text Data Types:
  • CHAR(n): Fixed-length string with a set number of characters.
  • VARCHAR(n): Variable-length string, useful for names, descriptions, and comments.
  • TEXT: Stores large text blocks, useful for articles, messages, and logs.
• Date and Time Data Types:
  • DATE: Stores date values (YYYY-MM-DD).
  • TIME: Stores time values (HH:MI:SS).
  • DATETIME: Stores date and time together.
  • TIMESTAMP: Stores a UTC-based timestamp, useful for tracking when records were created or updated.
• Boolean Data Type:
  • BOOLEAN: Stores TRUE or FALSE. Some databases use BIT(1) instead.
• Special Data Types:
  • BLOB: Stores binary data, such as images or files.
  • JSON: Stores structured data in JSON format.
  • UUID: Stores universally unique identifiers.
  • MONEY: Used in some SQL databases for currency values.

When to Use SQL Data Types

Ensuring Data Accuracy

Data types enforce rules on what kind of data can be stored. For instance, defining a column as DATE prevents non-date values from being inserted.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    hire_date DATE
);

Optimizing Storage and Performance

Choosing the right data type reduces unnecessary storage use and speeds up queries. Using a smaller integer type (SMALLINT instead of INT) for columns with limited possible values can save space.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id SMALLINT
);

Storing Large Text and Binary Data

When storing large amounts of text, TEXT is more efficient than VARCHAR for long-form content.

CREATE TABLE blog_posts (
    post_id INT PRIMARY KEY,
    content TEXT
);

For binary data like images or files, use BLOB.

CREATE TABLE images (
    image_id INT PRIMARY KEY,
    data BLOB
);

Examples of SQL Data Types in Action

Using INT and DECIMAL for Numeric Data

INT is great for counting or identifying records, while DECIMAL ensures precision for financial data.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2)
);

This structure ensures that price stores up to 10 digits with two decimal places.

Working with Variable-Length Text

For storing names and descriptions, VARCHAR is preferred because it only uses the needed space.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(255)
);

Storing Dates and Timestamps

To track when records were created or modified, TIMESTAMP is a useful choice.

CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Boolean Data Type for Flags

Many databases use BOOLEAN or BIT(1) to store TRUE or FALSE values.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE
);

This ensures that every new user starts as active unless specified otherwise.

Learn More About SQL Data Types

How to Alter a Column’s Data Type

To change the data type of a column, use ALTER TABLE:

ALTER TABLE employees ALTER COLUMN salary FLOAT;

For MySQL and PostgreSQL:

ALTER TABLE employees MODIFY salary FLOAT;

Checking a Column’s Data Type

To see a table’s column types:

• MySQL:

  Copy Code

  DESC employees;
  

• PostgreSQL:

  Copy Code

  SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'employees';
  

• SQL Server:

  Copy Code

  EXEC sp_columns employees;
  

Casting Data Types in Queries

Convert a column’s data type within a query:

SELECT CAST(salary AS FLOAT) FROM employees;

In SQL Server:

SELECT CONVERT(FLOAT, salary) FROM employees;

Using Numeric Data Types Effectively

Choosing the right numeric data type prevents unnecessary storage waste.

• Use INT for whole numbers like IDs and counters.
• Use DECIMAL(10,2) for money to avoid floating-point inaccuracies.
• Use FLOAT for scientific calculations where minor precision loss is acceptable.

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    amount DECIMAL(12,2)
);

Handling Dates in SQL

Date data types simplify operations like filtering by year or month.

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_date DATE
);

To find events in a specific year:

SELECT * FROM events WHERE YEAR(event_date) = 2024;

Using SQL Server MONEY Data Type

SQL Server offers MONEY for currency values, but DECIMAL is more portable.

CREATE TABLE salaries (
    salary MONEY
);

For cross-platform compatibility, use:

CREATE TABLE salaries (
    salary DECIMAL(10,2)
);

Optimizing Large Text Storage

Use TEXT when storing long-form content.

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    content TEXT
);

Changing Data Types Before Flattening Data

Flattening data (e.g., for reporting) often requires changing data types. You can do this dynamically in queries:

SELECT CAST(revenue AS DECIMAL(10,2)) FROM sales;

This ensures the correct data type before exporting or aggregating results.

Using Boolean Data Types in Different SQL Versions

While BOOLEAN works in some databases, others use BIT(1).

CREATE TABLE features (
    feature_id INT PRIMARY KEY,
    is_enabled BIT(1)
);

In PostgreSQL, BOOLEAN is preferred:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE
);

Handling Floating-Point Data

Use FLOAT carefully due to rounding errors.

CREATE TABLE measurements (
    measure_id INT PRIMARY KEY,
    value FLOAT
);

For precise decimal storage, use DECIMAL:

CREATE TABLE precise_data (
    data_id INT PRIMARY KEY,
    precise_value DECIMAL(10,5)
);

SQL data types are fundamental in database management. They affect performance, storage, and accuracy. Choosing the right type helps avoid common pitfalls like precision loss in floating points, inefficient text storage, or incorrect date handling.