SQL Numeric Data Types: INT, FLOAT, DECIMAL | Full Guide

Introduction

In SQL databases, numeric data types are essential for storing numbers like quantities, prices, salaries, measurements, and more. Choosing the right numeric type improves storage efficiency, query performance, and accuracy.

The most common numeric types are INT, FLOAT, and DECIMAL. In this section, you'll learn their definitions, differences, use cases, and examples to help you pick the best type for your database.

1. INT (Integer)

INT stands for integer. It is used to store whole numbers (numbers without fractions or decimals).

Features:

• Stores numbers without any decimal points.
• Takes 4 bytes of storage.
• Range (for signed INT): from -2,147,483,648 to 2,147,483,647.

Syntax:

column_name INT;

Example:

CREATE TABLE employees (
id INT,
age INT,
years_of_experience INT
     );

Here, age and years_of_experience are whole numbers — perfect for INT.

When to Use INT:

• Counting items (e.g., number of employees, product quantities).
• Storing age, ID numbers, or scores.

2. FLOAT (Floating-Point Number)

FLOAT is used to store approximate decimal values. It allows storing real numbers but with a potential loss of precision.

Features:

• Can store very large or very small numbers.
• Good for approximate scientific data.
• Size can vary (4 bytes for FLOAT, 8 bytes for DOUBLE in some systems).

Syntax:

column_name FLOAT;

Or you can specify precision:

FLOAT(p)

where p is the number of bits used to store the number.

Example:

CREATE TABLE products (
id INT,
weight FLOAT
    );

The weight may have decimal values like 2.56 kg or 15.789 kg.

When to Use FLOAT:

• Scientific calculations.
• Measurements (weight, height, temperature) where minor rounding errors are acceptable.
• Applications needing very large or very small numbers.

3. DECIMAL (Fixed-Point Number)

DECIMAL (also known as NUMERIC in some databases) is used for exact decimal values. It stores numbers with a fixed number of digits before and after the decimal point — without losing precision.

Features:

• Perfect for financial and monetary values.
• Defined with two parameters: precision and scale.
• Precision = total number of digits.
• Scale = number of digits after the decimal point.

Syntax:

column_name DECIMAL(precision, scale);

Example:

DECIMAL(10,2) = up to 10 digits total, with 2 digits after the decimal point.

Example:

CREATE TABLE accounts (
id INT,
balance DECIMAL(12, 2)
   );

Here, balance can store values like 12345678.90 exactly.

When to Use DECIMAL:

• Financial applications (money, currency).
• Where accuracy is critical (banking, billing).
• Storing prices, interest rates, percentages.

Quick Comparison: INT vs FLOAT vs DECIMAL

Important Tips

• Use INT whenever you are sure the number will not have a decimal part.
• Use FLOAT when performance and storage matter more than exact precision.
• Use DECIMAL when you need exactness, especially for money and financial data.
• Overusing FLOAT where DECIMAL is needed can lead to small but dangerous rounding errors.

Conclusion

Choosing the right numeric data type is crucial for database design and data accuracy. Use INT for whole numbers, FLOAT for approximate values, and DECIMAL for precise decimal values, especially when accuracy matters most.