Numeric Types - SQL Data Types | OneNoughtOne

0/278

SQLData Types

SQL Data Types

LevelBeginner

Duration60 mins

TopicData Types

1 / 5

Numeric Types

The Foundation of Data Precision

Every database stores numbers—sales figures, user counts, financial transactions, scientific measurements, coordinates. But how a number is stored determines everything: whether your financial calculations will be accurate to the penny, whether your scientific data preserves critical precision, whether your storage costs spiral out of control, and whether your queries execute efficiently.

Numeric types are not interchangeable. Using the wrong numeric type is one of the most common and costly mistakes in database design. This page provides an exhaustive examination of SQL numeric types, their characteristics, and the engineering judgment required to select the right type for every use case.

What You Will Learn

By the end of this page, you will understand the complete taxonomy of SQL numeric types, the critical distinction between exact and approximate numerics, precision and scale semantics, storage implications, performance characteristics, and the decision framework for selecting optimal numeric types in production systems.

The Numeric Type Taxonomy

SQL numeric types are divided into two fundamental categories, each serving distinct purposes and exhibiting fundamentally different behaviors:

1. Exact Numeric Types — Store numbers with complete precision; every digit is preserved exactly as specified. These are essential for financial calculations, counts, identifiers, and any domain where approximate values are unacceptable.

2. Approximate Numeric Types — Store numbers using floating-point representation, which provides a wide range but introduces potential rounding errors. These are designed for scientific calculations, statistical analysis, and scenarios where extreme precision is less critical than computational range.

SQL Numeric Type Classification
Category	Types	Precision	Primary Use Cases
Exact Integers	INTEGER, SMALLINT, BIGINT, TINYINT	Complete (no decimals)	Counts, IDs, flags, quantities
Exact Decimals	DECIMAL, NUMERIC	User-defined precision	Financial data, exact calculations
Approximate Floats	REAL, DOUBLE PRECISION, FLOAT	~7 or ~15 significant digits	Scientific data, statistics, geometry

The Critical Distinction

The choice between exact and approximate numerics is not arbitrary. Using FLOAT for currency will introduce rounding errors that compound over thousands of transactions. Using DECIMAL for scientific calculations wastes storage and may lack the range needed. Understanding this distinction is fundamental to database design.

Integer Types: Exact Whole Numbers

Integer types store whole numbers (no fractional component) with complete precision. They are the most commonly used numeric types in database systems, appearing in primary keys, counters, quantities, flags, and enumerated values.

The SQL standard defines three core integer types:

Type	Minimum Value	Maximum Value	Storage
SMALLINT	-32,768	32,767	2 bytes
INTEGER (INT)	-2,147,483,648	2,147,483,647	4 bytes
BIGINT	-9,223,372,036,854,775,808	9,223,372,036,854,775,807	8 bytes

These ranges derive directly from the underlying binary representation: a signed n-bit integer spans from -2^(n-1) to 2^(n-1)-1.

integer_types_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Integer type declarations in table definitions
CREATE TABLE products (
    product_id      INTEGER PRIMARY KEY,      -- 4 bytes, ~2.1 billion range
    category_code   SMALLINT NOT NULL,        -- 2 bytes, sufficient for category codes
    units_in_stock  INTEGER DEFAULT 0,        -- 4 bytes, handles warehouse quantities
    total_sold      BIGINT DEFAULT 0          -- 8 bytes, for cumulative lifetime sales
);
 
-- Integer ranges demonstration
-- SMALLINT: Good for bounded enumerations, status codes, small counts
CREATE TABLE order_status (
    status_id       SMALLINT PRIMARY KEY,     -- -32,768 to 32,767
    status_name     VARCHAR(50) NOT NULL
);
 
-- INTEGER: The workhorse type for most use cases
CREATE TABLE customers (
    customer_id     INTEGER PRIMARY KEY,       -- Handles billions of customers
    loyalty_points  INTEGER DEFAULT 0,         -- Accumulating points
    order_count     INTEGER DEFAULT 0          -- Lifetime orders
);
 
-- BIGINT: For values that may exceed ~2 billion
CREATE TABLE analytics_events (
    event_id        BIGINT PRIMARY KEY,        -- Global event counter
    user_id         INTEGER,                   -- User reference
    timestamp_ms    BIGINT NOT NULL            -- Unix timestamp in milliseconds
);

Integer Type Selection Guidelines

•Use SMALLINT for status codes, enumeration values, and quantities guaranteed to stay under 32,767. Common examples: age, rating (1-5), priority levels, months.
•Use INTEGER as the default choice for most whole-number columns including primary keys, foreign keys, counts, and quantities. This handles over 2 billion distinct values.
•Use BIGINT when values may exceed 2.1 billion: global identifiers in distributed systems, cumulative counters in high-volume systems, timestamps in milliseconds, or financial amounts in minor units (cents).
•Never use larger types 'just in case' — BIGINT uses 2x the storage of INTEGER, impacting index sizes, memory usage, and query performance.

Unsigned Integers

Some database systems (MySQL, MariaDB) support UNSIGNED integer variants that double the positive range by eliminating negative values. UNSIGNED INTEGER spans 0 to 4,294,967,295. PostgreSQL does not support unsigned types natively—use CHECK constraints or domain types to enforce non-negativity.

DECIMAL and NUMERIC: Exact Fixed-Point Arithmetic

DECIMAL and NUMERIC are exact numeric types that store numbers with a user-specified precision (total digits) and scale (digits after the decimal point). In the SQL standard, DECIMAL and NUMERIC are functionally equivalent, though some implementations treat them slightly differently.

Syntax: DECIMAL(precision, scale) or NUMERIC(precision, scale)

Precision (p): Total number of significant digits (1 to implementation max, typically 38)
Scale (s): Number of digits to the right of the decimal point (0 to p)

Examples:

DECIMAL(10, 2) — Up to 10 digits total, 2 after decimal → -99,999,999.99 to 99,999,999.99
DECIMAL(5, 0) — 5-digit integer → -99,999 to 99,999
DECIMAL(18, 4) — High precision with 4 decimal places → typical for financial calculations

decimal_numeric_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Financial data: DECIMAL is mandatory for accuracy
CREATE TABLE financial_transactions (
    transaction_id  INTEGER PRIMARY KEY,
    amount          DECIMAL(15, 2) NOT NULL,    -- Up to $9,999,999,999,999.99
    tax_rate        DECIMAL(5, 4) NOT NULL,     -- Up to 9.9999 (e.g., 0.0825 = 8.25%)
    tax_amount      DECIMAL(15, 2) NOT NULL,
    total_amount    DECIMAL(15, 2) NOT NULL
);
 
-- Currency exchange rates require higher precision
CREATE TABLE exchange_rates (
    rate_id         INTEGER PRIMARY KEY,
    base_currency   CHAR(3) NOT NULL,
    target_currency CHAR(3) NOT NULL,
    rate            DECIMAL(18, 8) NOT NULL,    -- 8 decimal places for precision
    effective_date  DATE NOT NULL
);
 
-- Product pricing with precise control
CREATE TABLE products (
    product_id      INTEGER PRIMARY KEY,
    cost_price      DECIMAL(12, 4) NOT NULL,    -- Internal cost with 4 decimals
    retail_price    DECIMAL(12, 2) NOT NULL,    -- Customer-facing with 2 decimals
    discount_pct    DECIMAL(5, 2) DEFAULT 0     -- Percentage (0.00 to 100.00)
);
 
-- Exact arithmetic demonstration
-- Unlike FLOAT, DECIMAL preserves exact values
SELECT 0.1 + 0.2;  -- Returns exactly 0.3 with DECIMAL
-- With FLOAT, this might return 0.30000000000000004
 
-- Precision matters in aggregations
SELECT SUM(amount) as total_revenue
FROM financial_transactions
WHERE transaction_date >= '2024-01-01';
-- DECIMAL ensures the sum is exact to the penny

The Classic Floating-Point Trap

In binary floating-point, 0.1 cannot be represented exactly—it becomes 0.1000000000000000055511151231257827021181583404541015625. After millions of financial transactions, these tiny errors compound into significant discrepancies. DECIMAL avoids this entirely by using decimal-based (base-10) representation.

When to Use DECIMAL/NUMERIC

•Financial amounts (currency, prices, costs)
•Tax calculations and percentages
•Interest rates and financial ratios
•Inventory quantities with partial units
•Any domain requiring exact decimal arithmetic
•Regulatory compliance (banking, accounting)

When NOT to Use DECIMAL/NUMERIC

•Scientific measurements (use DOUBLE)
•Geographic coordinates (use DOUBLE)
•Statistical calculations (use DOUBLE)
•Graphics/geometry computations
•Performance-critical calculations at scale
•Values requiring very large or small magnitudes

Precision and Scale: The Complete Picture

Understanding precision and scale is essential for correct DECIMAL type specification. Incorrect settings lead to data truncation, insertion failures, or wasted storage.

Definitions:

Precision: The total count of significant digits in the entire number (both sides of decimal)
Scale: The count of digits to the right of the decimal point
Implicit Integer Digits: precision - scale = digits available to the left of decimal

Example Analysis:

DECIMAL(7, 2)
├── Precision: 7 (total significant digits)
├── Scale: 2 (digits after decimal)
├── Integer digits: 7 - 2 = 5 (digits before decimal)
├── Range: -99,999.99 to 99,999.99
└── Examples: 12345.67, -99999.99, 0.01

Precision and Scale Examples
Declaration	Precision	Scale	Integer Digits	Valid Range	Example Values
DECIMAL(5, 2)	5	2	3	-999.99 to 999.99	123.45, -999.99
DECIMAL(10, 0)	10	0	10	-9,999,999,999 to 9,999,999,999	1234567890
DECIMAL(3, 3)	3	3	0	-0.999 to 0.999	0.123, -0.999
DECIMAL(18, 4)	18	4	14	±99,999,999,999,999.9999	12345678901234.5678
DECIMAL(38, 10)	38	10	28	Maximum precision for most DBs	Scientific calculations

precision_scale_behavior.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Demonstrating precision and scale behavior
 
-- Case 1: Value fits within specification
CREATE TABLE price_test (price DECIMAL(7, 2));
INSERT INTO price_test VALUES (12345.67);  -- ✓ Works: 7 total digits, 2 decimal
INSERT INTO price_test VALUES (99999.99);  -- ✓ Works: Maximum value
INSERT INTO price_test VALUES (0.01);      -- ✓ Works: Minimum positive
 
-- Case 2: Exceeding integer digits (left of decimal)
INSERT INTO price_test VALUES (123456.78);  -- ✗ ERROR: 8 digits, only 7 allowed
-- Error: numeric field overflow
 
-- Case 3: Exceeding scale (right of decimal) - ROUNDING occurs
INSERT INTO price_test VALUES (123.456);    -- Stored as 123.46 (rounded)
INSERT INTO price_test VALUES (123.454);    -- Stored as 123.45 (rounded down)
-- Note: Some databases error instead of rounding (configurable)
 
-- Case 4: Scale equals precision (fractional-only values)
CREATE TABLE percentage (pct DECIMAL(3, 3));
INSERT INTO percentage VALUES (0.125);      -- ✓ Valid: 0.125
INSERT INTO percentage VALUES (1.0);        -- ✗ ERROR: Needs integer digit
 
-- Case 5: Zero scale (effectively an integer)
CREATE TABLE large_int (value DECIMAL(15, 0));
INSERT INTO large_int VALUES (123456789012345);  -- ✓ Works
INSERT INTO large_int VALUES (123.45);           -- Stored as 123 (truncated)
 
-- Storage implications vary by database:
-- PostgreSQL: Variable storage (2 bytes + n bytes based on value)
-- SQL Server: 5-17 bytes based on precision
-- MySQL: Approximately (precision/2) + 1 bytes

Choosing Precision and Scale

Always allow headroom for growth. If current prices max at $9,999.99, don't use DECIMAL(6,2)—use DECIMAL(10,2) or higher. The storage difference is minimal, but schema changes to increase precision require table rebuilds.

Floating-Point Types: Approximate Numerics

Floating-point types (REAL, DOUBLE PRECISION, FLOAT) store approximate numeric values using the IEEE 754 standard. They excel at representing very large or very small numbers within a fixed storage footprint, but sacrifice exact precision for range.

The SQL Standard Types:

Type	Storage	Precision	Range (approximate)
REAL	4 bytes	~7 significant digits	±3.4 × 10^38
DOUBLE PRECISION	8 bytes	~15 significant digits	±1.8 × 10^308
FLOAT(n)	Varies	n bits of mantissa	Depends on n

How Floating-Point Works:

A floating-point number is stored as: sign × mantissa × 2^exponent

This representation allows compact storage of both 0.000000001 and 1,000,000,000, but only a fixed number of significant digits are preserved.

floating_point_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Floating-point type declarations
CREATE TABLE scientific_data (
    measurement_id  INTEGER PRIMARY KEY,
    temperature     REAL,                   -- ~7 significant digits
    pressure        DOUBLE PRECISION,       -- ~15 significant digits
    velocity        DOUBLE PRECISION,
    measurement_error REAL
);
 
-- Geographic coordinates (floating-point is appropriate)
CREATE TABLE locations (
    location_id     INTEGER PRIMARY KEY,
    latitude        DOUBLE PRECISION NOT NULL,  -- -90.0 to 90.0
    longitude       DOUBLE PRECISION NOT NULL,  -- -180.0 to 180.0
    altitude_meters REAL                        -- Altitude with ~7 digits precision
);
 
-- Statistical data
CREATE TABLE statistics (
    stat_id         INTEGER PRIMARY KEY,
    mean            DOUBLE PRECISION,
    std_deviation   DOUBLE PRECISION,
    variance        DOUBLE PRECISION,
    p_value         DOUBLE PRECISION           -- Often very small numbers
);
 
-- Scientific notation handling (automatic)
INSERT INTO scientific_data (measurement_id, temperature, pressure)
VALUES (1, 2.998e8, 1.01325e5);  -- Speed of light, atmospheric pressure
 
-- Demonstrating precision limitations
SELECT CAST(0.1 AS REAL) + CAST(0.2 AS REAL);
-- Result: 0.30000001192092896 (not exactly 0.3)
 
SELECT CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION);
-- Result: 0.30000000000000004 (closer, but still not exact)

Floating-Point Comparison Pitfalls

Never compare floating-point values for exact equality. WHERE price = 19.99 can fail even when you inserted 19.99. Instead, compare within a tolerance: WHERE ABS(price - 19.99) < 0.001. This is a fundamental characteristic of floating-point arithmetic.

Appropriate Use Cases for Floating-Point

•Scientific measurements — Temperature, pressure, velocity, where measurement error exceeds representation error
•Geographic coordinates — Latitude, longitude, altitude (DOUBLE PRECISION provides ~1mm precision at equator)
•Statistical values — Mean, standard deviation, p-values, especially when values span many orders of magnitude
•Machine learning features — Model weights, activations, normalized values
•Graphics and geometry — 3D coordinates, transformations, color values
•Performance-critical numeric computation — When DECIMAL computation overhead is unacceptable

Storage and Performance Considerations

Numeric type selection directly impacts storage requirements, index sizes, and query performance. Understanding these implications is essential for designing efficient database schemas.

Storage Comparison (typical implementations):

Numeric Type Storage Requirements
Type	Storage	Use Case	Performance Characteristics
TINYINT	1 byte	Flags, small codes	Fastest integer operations
SMALLINT	2 bytes	Small ranges, codes	Excellent performance
INTEGER	4 bytes	General purpose	Optimal balance
BIGINT	8 bytes	Large values	2× INTEGER storage impact
REAL	4 bytes	Scientific, approximate	Hardware-accelerated
DOUBLE PRECISION	8 bytes	High-precision float	Hardware-accelerated
DECIMAL(p ≤ 9)	5 bytes	Small precision	Software arithmetic
DECIMAL(10 ≤ p ≤ 19)	9 bytes	Medium precision	Software arithmetic
DECIMAL(20 ≤ p ≤ 38)	17 bytes	High precision	Slower computation

storage_impact.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Example: Impact of numeric type on table size
-- Scenario: 100 million row transaction table
 
-- Option A: Using BIGINT everywhere
CREATE TABLE transactions_bigint (
    id          BIGINT,           -- 8 bytes
    user_id     BIGINT,           -- 8 bytes
    amount      BIGINT,           -- 8 bytes (storing cents)
    status      BIGINT            -- 8 bytes
);
-- Row size: ~32 bytes numeric data
-- Table size: ~3.2 GB just for these columns
 
-- Option B: Appropriate type sizing
CREATE TABLE transactions_optimized (
    id          BIGINT,           -- 8 bytes (global unique)
    user_id     INTEGER,          -- 4 bytes (2B users sufficient)
    amount      DECIMAL(12, 2),   -- 5-9 bytes
    status      SMALLINT          -- 2 bytes (< 100 statuses)
);
-- Row size: ~19-23 bytes numeric data
-- Table size: ~2 GB (35% smaller)
 
-- Index size impact
-- An index on BIGINT is 2× the size of INTEGER index
-- For 100M rows: INTEGER index ~400MB vs BIGINT index ~800MB
 
-- Query performance implications
-- Smaller types = more rows fit in memory/cache
-- Smaller indexes = faster index scans
-- INTEGER comparisons typically equal or faster than BIGINT
 
-- DECIMAL computation overhead
-- DECIMAL arithmetic is software-computed (slower)
-- FLOAT/DOUBLE use CPU floating-point unit (faster)
-- For aggregations over millions of rows, this adds up
 
SELECT SUM(amount) FROM transactions_optimized;  -- DECIMAL: slower
SELECT SUM(amount) FROM scientific_data;          -- DOUBLE: faster

The 80/20 Rule of Numeric Optimization

Focus optimization on high-volume tables and frequently-accessed columns. A 4-byte savings per row in a billion-row table is 4GB of storage and significant I/O reduction. The same savings in a 1000-row lookup table is irrelevant.

Vendor-Specific Integer Extensions

While the SQL standard defines core numeric types, major database systems extend these with vendor-specific types optimized for particular use cases:

•SERIAL, BIGSERIAL, SMALLSERIAL — Auto-incrementing integers (wrapper for INTEGER + sequence)
•NUMERIC — Equivalent to DECIMAL, arbitrary precision up to 1000 digits
•MONEY — Currency type with locale-aware formatting (not recommended—use DECIMAL)
•INT2, INT4, INT8 — Aliases for SMALLINT, INTEGER, BIGINT
•FLOAT4, FLOAT8 — Aliases for REAL, DOUBLE PRECISION

1
2
3
4
5
6
7
8
9
10
11
12
13
-- PostgreSQL-specific examples
CREATE TABLE pg_demo (
    id SERIAL PRIMARY KEY,           -- Auto-incrementing INTEGER
    big_id BIGSERIAL UNIQUE,         -- Auto-incrementing BIGINT
    balance NUMERIC(20, 2),          -- Alias for DECIMAL
    latitude FLOAT8                  -- Alias for DOUBLE PRECISION
);
 
-- PostgreSQL numeric functions
SELECT pg_typeof(100);              -- Returns 'integer'
SELECT pg_typeof(100.5);            -- Returns 'numeric'
SELECT pg_column_size(100::int);    -- Returns 4 (bytes)
SELECT pg_column_size(100::bigint); -- Returns 8 (bytes)

Summary and Best Practices

Numeric type selection is a foundational database design decision with long-term implications for data integrity, storage efficiency, and query performance. Let's consolidate the key principles:

Key Takeaways

•Exact vs. Approximate is fundamental — Use DECIMAL/NUMERIC for financial data and exact calculations; use FLOAT/DOUBLE for scientific and statistical data where precision has inherent uncertainty.
•Right-size integers — Use SMALLINT, INTEGER, or BIGINT based on actual value ranges, not assumptions. BIGINT everywhere wastes storage and index space.
•Understand precision and scale — DECIMAL(p, s) allows p total digits with s after the decimal. Plan for growth—changing precision later is expensive.
•Floating-point has limits — Never use FLOAT for currency. Comparisons require tolerance checks. Representation errors accumulate in aggregations.
•Storage impacts everything — Smaller types mean more rows per page, smaller indexes, better cache utilization, and faster queries at scale.
•Know your platform — Vendor-specific types (SERIAL, MONEY, NUMBER) offer convenience but impact portability. Document choices for future maintainers.

Quick Reference: Numeric Type Selection
Data Domain	Recommended Type	Rationale
Currency/Financial	DECIMAL(15, 2) or higher	Exact arithmetic, no rounding errors
Primary Keys	INTEGER or BIGINT	Fast comparisons, compact indexes
Counts/Quantities	INTEGER (or SMALLINT)	Exact whole numbers
Percentages	DECIMAL(5, 2) or (5, 4)	Exact percentage representation
Geographic Coords	DOUBLE PRECISION	Sufficient precision for submeter accuracy
Scientific Data	DOUBLE PRECISION	Wide range, hardware-accelerated
Boolean/Flags	SMALLINT or BIT	Minimal storage, clear semantics

Page Complete

You now have comprehensive knowledge of SQL numeric types. You understand the exact/approximate distinction, precision and scale semantics, storage implications, and vendor-specific extensions. Next, we'll explore character and string types—equally critical for storing textual data correctly.