SQL Numeric Data Types
Introduction
SQL numeric data types store values that participate in counting, arithmetic, comparison, aggregation, sorting, and statistical analysis. Choosing a numeric type is a data-modeling decision: it determines whether values are exact or approximate, what range and decimal scale are supported, how calculations behave, and how applications exchange the data.
A good design begins with business meaning rather than the appearance of a sample value. An order quantity, product price, scientific measurement, generated identifier, percentage, and postal code require different decisions even when each appears to contain digits.
What Are Numeric Data Types?
A numeric data type defines the set of numbers a column or expression can represent and the rules used to store and calculate them. SQL databases generally provide three families:
- Integer types store whole numbers exactly.
- Exact decimal types store fixed-point decimal values within declared precision and scale.
- Approximate floating-point types represent a wide range using binary scientific notation and finite significant precision.
Type names, storage sizes, ranges, defaults, and arithmetic rules vary by database. Always confirm the target product and version.
Why SQL Provides Multiple Numeric Types
No single numeric representation is ideal for every domain. Whole-number counters do not need fractional storage. Financial values need decimal exactness. Scientific measurements may need enormous range and accept approximation. High-volume identifiers may outgrow ordinary integer ranges.
Choosing an appropriate type supports correctness, constraints, index size, row density, application mapping, and future growth. Choosing blindly can produce overflow, rounding differences, wasted storage, or difficult migrations.
Exact Versus Approximate Numbers
| Family | Typical types | Representation | Common uses |
|---|---|---|---|
| Integer | SMALLINT, INTEGER, BIGINT | Exact whole numbers | Counts, quantities, IDs |
| Exact decimal | DECIMAL(p,s), NUMERIC(p,s) | Exact decimal digits within declaration | Money, rates, contractual values |
| Approximate | REAL, FLOAT, DOUBLE PRECISION | Approximate floating point | Measurements, simulation, analytics |
Exact does not mean unlimited. Integer and decimal values still have range and precision limits. Approximate does not mean random; it means some decimal values cannot be represented exactly in the underlying floating-point format.
Integer Data Types
Integer types store positive, negative, and zero whole numbers without fractional parts. Common standard-style names are SMALLINT, INTEGER or INT, and BIGINT.
employee_count INTEGER
stock_quantity INTEGER
event_id BIGINT
INT is commonly a synonym for INTEGER. Exact ranges and storage are product-defined, so do not assume every database implements each name identically.
SMALLINT, INTEGER, and BIGINT
SMALLINT is intended for a relatively limited whole-number range. INTEGER is the common general-purpose choice. BIGINT supports substantially larger whole numbers and is appropriate for high-growth counters or identifiers.
The smallest type that safely covers current and credible future values can reduce row and index size. However, selecting a range too close to today's maximum can create a risky migration later. Capacity planning matters more than micro-optimizing one column.
Range, Overflow, and Underflow
Overflow occurs when a value or arithmetic result exceeds the type's supported maximum or minimum. The database may raise an error, reject a write, or behave according to product and configuration rules. Silent wrapping should never be assumed safe.
Underflow is discussed more often with floating point, where a nonzero magnitude can become too small to represent normally. For signed integer design, developers usually mean exceeding the negative minimum when they informally say underflow.
Test calculations as well as stored inputs. Two valid integer operands can produce a sum or product outside the result type's range.
Choosing an Integer Type
Estimate minimum, maximum, growth rate, retention period, and arithmetic operations. An age column might fit in a small range, but a globally generated event ID could need BIGINT. A stock quantity may require negatives if backorders are represented that way, or a check constraint may forbid them.
Do not use BIGINT automatically everywhere. Wider keys enlarge indexes, foreign keys, joins, caches, and network payloads. Use it when the domain or growth model justifies it.
Digits Do Not Automatically Mean Numeric
A value should be numeric when arithmetic, numeric range comparison, or numeric aggregation is meaningful. Phone numbers, postal codes, product codes, account references, and government identifiers may contain only digits but are usually labels.
postal_code VARCHAR(20)
phone_number VARCHAR(30)
external_account_code VARCHAR(40)
Storing these as integers can remove leading zeros, reject formatting symbols, impose an irrelevant range, and encourage meaningless arithmetic.
Leading Zeros and Identifier Semantics
The text '00125' and the integer 125 represent the same numeric magnitude but may be different business identifiers. If length, position, prefixes, or leading zeros are significant, use a character type and enforce the identifier format separately.
DECIMAL and NUMERIC
DECIMAL and NUMERIC represent exact decimal values. In many databases they are synonyms or nearly equivalent; the SQL standard permits subtle distinctions in guaranteed precision behavior, while products generally document their own implementation.
unit_price DECIMAL(12, 2)
tax_rate DECIMAL(7, 4)
exchange_rate DECIMAL(20, 10)
These types are central to financial and contractual systems because values such as 0.1 can be represented exactly within the declared decimal precision.
Precision and Scale
Precision is the total number of decimal digits. Scale is the number of digits to the right of the decimal point.
DECIMAL(10, 2)
This declaration allows ten total digits, two of them fractional, leaving up to eight digits before the decimal point. Precision is not the same as storage bytes, and scale is not the total number of digits.
Understanding DECIMAL(10,2)
| Value | Conceptual result | Reason |
|---|---|---|
12345678.90 | Fits | Eight integral and two fractional digits |
123456789.00 | Does not fit | Nine integral digits exceed capacity |
12.345 | Rounded or rejected by product rules | Three fractional digits exceed scale |
Database behavior for excess scale can depend on product, mode, and operation. Do not rely on implicit rounding when the business rule requires a specific method.
DECIMAL Without Explicit Scale
A declaration such as DECIMAL or DECIMAL(p) can have database-specific defaults. Some products provide large or variable precision, while others apply a default scale. Portable schemas should state required precision and scale explicitly after checking product limits.
Why DECIMAL Matters for Money
Financial systems need repeatable decimal arithmetic and auditable rounding. A common column might be:
amount DECIMAL(19, 4)
The right precision and scale depend on currency, maximum transaction or ledger balance, intermediate calculations, regulatory requirements, and rounding policy. DECIMAL(10,2) is not a universal money type.
Financial Calculation Example
SELECT quantity,
unit_price,
tax_rate,
quantity * unit_price AS subtotal,
ROUND(quantity * unit_price * tax_rate, 2) AS tax_amount
FROM invoice_items;
Define whether rounding happens per line, per tax category, or only on the invoice total. Those methods can produce different valid totals. The database type supports exact decimal values, but business policy defines calculation order and rounding.
Floating-Point Types
REAL, FLOAT, and DOUBLE PRECISION are approximate types. They typically use binary floating-point representations similar to scientific notation: a sign, significant digits, and an exponent.
Floating point provides a wide magnitude range with efficient hardware arithmetic, but many simple decimal fractions cannot be represented exactly. Stored or calculated values may differ slightly from their decimal display.
REAL, DOUBLE PRECISION, and FLOAT
REAL commonly represents lower-precision floating point and DOUBLE PRECISION higher precision. FLOAT(p) is especially dialect-sensitive: its parameter may describe binary precision, select an implementation size, or follow product-specific rules.
Do not infer portability from the type name alone. Check precision, storage, range, special values, and client mappings for the target database.
Why FLOAT Is Usually Wrong for Money
Approximate representation can create small residual differences during addition, multiplication, equality testing, and aggregation. Those differences are unacceptable when amounts must reconcile exactly to defined decimal units.
Use exact decimal or a documented integer-minor-unit model for money. Floating point remains appropriate for domains where approximation is inherent and tolerances are part of the requirement.
Floating-Point Equality
Direct equality after floating-point calculations is often fragile:
-- Fragile after approximate calculations
WHERE measured_value = 0.3
A tolerance comparison may better express the domain:
WHERE ABS(measured_value - :expected_value) < :tolerance
The tolerance must come from measurement and business requirements, not an arbitrary constant. Relative tolerance may be more suitable across different magnitudes.
Precision Versus Range
Floating-point range describes how large or small a magnitude can be. Precision describes how many significant digits are reliable. A type can represent an enormous magnitude while retaining only a limited number of meaningful digits.
At sufficiently large magnitudes, consecutive integers may no longer be individually representable by a floating-point type. This is one reason generated integer identifiers should remain integer values throughout APIs and applications.
Special Floating Values
Some databases and types support special values such as infinity, negative infinity, or not-a-number. Ordering, equality, aggregates, indexes, and client libraries can treat these values differently. If the business domain does not allow them, reject them through validation or constraints rather than assuming only ordinary finite numbers will arrive.
Business Meaning Determines the Type
| Use case | Likely family | Design question |
|---|---|---|
| Inventory quantity | Integer or exact decimal | Can partial units or negatives occur? |
| Currency amount | Exact decimal or integer minor units | What scale and rounding rules apply? |
| Scientific sensor value | Floating point or exact decimal | Is approximation acceptable? |
| Generated row ID | Integer | What growth and integration range is required? |
| Percentage | Exact decimal | Is 15% stored as 15 or 0.15? |
| Postal code | Character | Are digits identifiers rather than quantities? |
Percentage Storage
A percentage can be stored as a displayed percentage such as 15.0000 or as a fraction such as 0.150000. Both can work, but every expression, API, report, and constraint must follow one documented convention.
discount_rate DECIMAL(7, 6)
CHECK (discount_rate BETWEEN 0 AND 1)
This example uses fractional storage and allows six decimal places. The required range and scale depend on the domain.
Numeric Constraints
A type's technical range is not the business range. An integer may permit negative inventory even when the application forbids it. Add constraints that express valid domain values.
quantity INTEGER NOT NULL
CHECK (quantity >= 0),
discount_rate DECIMAL(7, 6)
CHECK (discount_rate BETWEEN 0 AND 1)
Constraints protect data regardless of which application performs the write.
Signed and Unsigned Numbers
Standard SQL integer concepts are signed. MySQL provides unsigned variants that trade negative range for a larger nonnegative maximum. PostgreSQL and SQL Server do not provide matching general-purpose unsigned integer families.
Unsigned syntax therefore reduces portability. A check constraint can enforce nonnegative values but does not enlarge a signed type's positive range.
Database-Specific Integer Types
MySQL offers types such as TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, with signed and unsigned options. PostgreSQL provides smallint, integer, and bigint, plus identity and historical serial conveniences. SQL Server provides tinyint, smallint, int, and bigint.
Names that appear similar can still differ in signedness, range, identity support, and storage. Schema portability requires a deliberate mapping rather than textual copying.
Oracle NUMBER
Oracle commonly uses NUMBER for integer-like and decimal values, optionally with precision and scale. A declared NUMBER(p,0) represents an exact whole-number domain within that precision, while NUMBER(p,s) supports a decimal scale.
Oracle also provides binary floating-point types. Map conceptual domains rather than assuming NUMBER behaves exactly like every other product's DECIMAL declaration.
SQLite Numeric Typing
SQLite uses dynamic typing with storage classes and column affinities rather than rigid per-column storage in the same way as many server databases. Integer and real values can be stored based on the actual value and affinity rules.
Declaring DECIMAL does not automatically create arbitrary-precision fixed-decimal arithmetic. Applications requiring exact money behavior need a deliberate strategy, such as integer minor units, validated text with dedicated decimal handling, or an appropriate extension.
Money as Integer Minor Units
Some systems store money as an integer count of the smallest supported unit:
amount_minor BIGINT
This provides exact integer arithmetic and clear transport across languages, but it requires currency metadata and a defined scale. Not every currency has two decimal places, and some products require sub-minor precision. Conversion and presentation must remain consistent.
Multi-Currency and Cryptocurrency Values
A numeric amount without its currency or asset code is incomplete. Store the amount together with a currency or asset identifier and define scale rules per domain.
Cryptocurrency values can require many decimal places and large aggregate balances. Choose precision from supported units, maximum exposure, fees, and calculation intermediates rather than reusing a conventional two-decimal money type.
Scientific Measurements
Floating point is useful when measurements are approximate, cover a broad range, and naturally use tolerances. Exact decimal may be preferred when instruments report fixed decimal increments, compliance requires decimal reproduction, or comparisons use contractual thresholds.
Store units alongside values or make the unit unambiguous in schema design. A precise number with an unknown unit is still unreliable data.
Latitude and Longitude
Coordinates can use fixed decimal values when consistent decimal precision is important, floating point for many computational workloads, or specialized spatial types for indexing and geographic operations. The correct choice depends on accuracy, coordinate system, calculations, and database capabilities.
Numeric Type Conversion
Explicit conversion documents the intended result type:
CAST(quantity AS DECIMAL(12, 2))
CAST(rounded_amount AS INTEGER)
Integer-to-decimal conversion can preserve the value when precision is sufficient. Decimal-to-integer conversion can round, truncate, or reject values depending on the database and function. Test the exact target behavior.
Implicit Conversion and Type Precedence
When operands have different numeric types, the database may choose a common type according to type-precedence rules. This can affect precision, scale, overflow, and index use.
WHERE numeric_id = :text_parameter
If the application binds a text parameter, the database may convert the parameter or the column. A conversion on the indexed column can be expensive, and invalid text can fail. Bind parameters using compatible numeric types.
Numeric Literals and Scientific Notation
The literal 10 is usually interpreted as an integer-like value, while 10.25 is decimal-like. A literal such as 1.2E6 commonly denotes scientific notation and may lead to an approximate type.
Literal typing rules are database-specific and can influence expression results. Cast important constants explicitly when precision and scale must be controlled.
Arithmetic Result Types
The result type of addition, subtraction, multiplication, and division is derived from operand types and product rules. Decimal multiplication may increase required precision and scale. Division often needs more fractional digits than either input.
CAST(numerator AS DECIMAL(20, 6))
/ NULLIF(denominator, 0)
Design intermediate precision as carefully as storage precision. A final column can be wide enough while an earlier expression overflows or rounds.
Integer Division
In several databases, division between integers produces an integer result or otherwise follows dialect-specific rules:
5 / 2
Do not assume this returns 2.5. Convert an operand to an exact decimal type when a fractional result is required.
Division by Zero
A zero denominator can raise an error. NULLIF can represent an undefined result as NULL:
revenue_amount / NULLIF(order_count, 0)
This is correct only when unknown or undefined is the intended meaning. Replacing the result with zero can hide a data or calculation problem.
Rounding, Truncation, and Storage Scale
Rounding changes a value according to a specified rule; truncation removes excess fractional digits. Assigning to a lower-scale column may trigger automatic rounding or rejection according to database behavior.
Financial systems must define midpoint handling, negative-number behavior, calculation sequence, and stage of rounding. Use explicit functions and tests rather than treating a column's scale as the complete rounding policy.
DEFAULT, NOT NULL, Zero, and NULL
quantity INTEGER NOT NULL DEFAULT 0
This declaration is appropriate only if zero is the correct default. Zero means a known numeric amount; NULL means missing, unknown, or not applicable. Substituting zero for unknown data changes business meaning and affects averages, counts, and reports.
Numeric Primary and Foreign Keys
Numeric integers are common surrogate primary keys because comparisons and indexes are compact and efficient. Every referencing foreign key should use a compatible type, range, and signedness.
Using a wider foreign key than the parent or mixing signed and unsigned definitions can create conversion, portability, and constraint problems. Key type changes affect every referencing table and index.
Generated Numeric Keys
Database products provide standard identity syntax or product features such as MySQL AUTO_INCREMENT, SQL Server IDENTITY, PostgreSQL identity columns, and Oracle identity or sequence patterns.
Generated keys are normally unique, not guaranteed gapless. Rollbacks, caching, concurrent allocation, restarts, and failed inserts can create gaps. Do not use surrogate key sequences when legal or accounting rules demand gapless document numbering.
Aggregates and Numeric Types
COUNT, SUM, and AVG return numeric results, but exact return types differ by input type and database. A database may widen an integer sum, retain a decimal family, or return an approximate result for floating input.
Large groups can make SUM overflow even when every row value is valid. AVG introduces fractional precision. Verify documented return types and cast before aggregation if the required capacity is larger.
Numeric Sorting and Comparison
Numeric columns sort by numeric magnitude, so 2 comes before 10. Character values sort according to collation, which can place '10' before '2'. Storing genuine quantities as text therefore produces incorrect range filters and ordering unless every query converts them.
Formatting Is Not Storage
Thousands separators, currency symbols, localized decimal separators, percentage signs, and display padding belong in presentation or formatting layers.
-- Store
12345.67
-- Display according to locale
$12,345.67
Formatted text is difficult to calculate, compare, index, and validate. Locale rules should not determine the internal numeric representation.
Numeric Type Portability
INTEGER, BIGINT, DECIMAL, and NUMERIC express broadly portable concepts, but limits and behavior still vary. Approximate types, unsigned integers, identity generation, special floating values, arbitrary precision, and storage details are more product-specific.
A migration should test schema mapping, out-of-range values, rounding, aggregate return types, implicit conversion, drivers, and application serialization. Type synonyms alone do not guarantee equivalent semantics.
Application-Language Mapping
Database numeric values pass through drivers and language types. Java commonly maps exact decimals to BigDecimal and wide integers to long or BigInteger when necessary. Python integers can grow beyond fixed machine ranges, while decimal.Decimal supports decimal arithmetic. Mappings still depend on the driver and ORM.
A correct database type can lose safety if an application maps it to an approximate or narrower type. Validate the complete path from database to API, browser, report, and back.
JavaScript and Large Integer APIs
JavaScript's ordinary Number type cannot exactly represent every large 64-bit integer. A database BIGINT ID can therefore change if parsed as an ordinary JSON number in some clients.
APIs often serialize large identifiers as strings, use a BigInt-aware protocol or library, or enforce a range safely representable by every consumer. This transport choice should be part of schema and contract design.
ORM Numeric Mappings
Object-relational mappers infer or generate numeric declarations from application models, but defaults may not match business needs. A decimal property without explicit precision and scale can produce inconsistent schemas across environments or migrations.
Specify the database type deliberately, review generated migrations, and test boundary values. Do not assume the ORM's default decimal precision is universally safe.
Numeric Indexes and Predicates
Numeric columns are commonly indexed for equality, range, ordering, and joins. Direct compatible comparisons are generally index-friendly:
WHERE total_amount >= :minimum_amount
AND total_amount < :maximum_amount
Applying arithmetic or a function to the indexed column can make a normal index less useful:
-- Potentially less index-friendly
WHERE total_amount * 1.2 > :threshold
Rewrite safely when algebra and NULL behavior permit, or use a matching generated-column or expression index supported by the database.
Numeric Statistics and Partitioning
Statistics help the optimizer estimate numeric ranges and value frequencies. Skewed values, correlated columns, and stale statistics can produce poor cardinality estimates.
Numeric range partitioning can organize very large tables, but boundaries must match query patterns and future growth. Partitioning is not a replacement for indexes, constraints, or archiving strategy.
Numeric Values in JSON
JSON has a general number syntax but does not itself guarantee the database's decimal precision or integer range. Parsers may map every number to binary floating point, preserving neither large integer IDs nor exact decimal amounts.
Define API contracts for numeric range, precision, scale, and string representation where required. Validate before converting JSON values into database columns.
Common Numeric Design Mistakes
- Using floating point for money or exact contractual values.
- Storing genuine quantities in character columns.
- Using an integer for measurements that require fractional values.
- Applying
DECIMAL(10,2)to every monetary or rate field. - Selecting an identifier range that cannot support future growth.
- Using numeric types for phone numbers, postal codes, or formatted identifiers.
- Testing computed floating-point values with exact equality.
- Confusing zero with missing data.
- Ignoring arithmetic and aggregate overflow.
- Relying on implicit conversion between strings and numbers.
- Storing currency symbols and separators with numeric data.
- Assuming integer sizes and
FLOATsemantics are universal. - Confusing precision, scale, and physical storage.
- Expecting a numeric type alone to enforce business validity.
Numeric Type Decision Process
- Decide whether the value is a quantity or an identifier.
- Determine whether fractional values are possible.
- Choose exact or approximate semantics.
- Estimate credible minimum and maximum values.
- Define required decimal places and intermediate precision.
- Account for future growth and aggregate calculations.
- Decide whether negative values and
NULLare valid. - Confirm database, driver, language, ORM, and API mappings.
- Add business constraints and explicit rounding rules.
- Test boundaries, conversion, overflow, and portability.
Example Product Table
CREATE TABLE products (
product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
unit_price DECIMAL(19, 4) NOT NULL,
stock_quantity INTEGER NOT NULL DEFAULT 0,
discount_rate DECIMAL(7, 6),
CHECK (unit_price >= 0),
CHECK (stock_quantity >= 0),
CHECK (discount_rate IS NULL
OR discount_rate BETWEEN 0 AND 1)
);
Exact type and identity syntax may need adjustment for the target database. Each type and constraint reflects a domain decision rather than a generic numeric default.
Example Scientific Table
CREATE TABLE sensor_readings (
reading_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sensor_id BIGINT NOT NULL,
measured_value DOUBLE PRECISION NOT NULL,
uncertainty DOUBLE PRECISION,
recorded_at TIMESTAMP NOT NULL,
CHECK (uncertainty IS NULL OR uncertainty >= 0)
);
Approximate values are reasonable when the physical measurement and uncertainty are already approximate. The schema still records unit and sensor meaning elsewhere in the model.
Example Financial Table
CREATE TABLE ledger_entries (
entry_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_id BIGINT NOT NULL,
currency_code CHAR(3) NOT NULL,
amount DECIMAL(24, 8) NOT NULL,
posted_at TIMESTAMP NOT NULL
);
The shown precision is illustrative, not universal. A real ledger must define supported currencies, rounding, posting rules, aggregate balance capacity, and application decimal mappings.
Testing Numeric Columns
Test zero, positive and negative boundaries, exact minimum and maximum allowed business values, values just outside the range, excess fractional digits, NULL, aggregate totals, and calculation intermediates.
For floating point, test tolerance behavior, very large and very small magnitudes, special values where supported, and serialization through every client. For decimals, test midpoint rounding, multiplication, division, and assignments to lower-scale columns.
Numeric Design Checklist
- Use integer types for exact whole-number quantities and keys.
- Use exact decimal types for money, rates, and contractual decimals.
- Use floating point only when approximation is acceptable.
- Document whether percentages are fractions or displayed percentages.
- Size ranges for credible growth without choosing maximum width blindly.
- Keep digit-based identifiers as character data when arithmetic is meaningless.
- Specify precision, scale, units, currency, and rounding policy explicitly.
- Match primary and foreign key types.
- Use constraints for business ranges and required values.
- Bind parameters with compatible numeric types.
- Review aggregate and intermediate result capacity.
- Verify driver, ORM, JSON, and language mappings.
Interview Questions and Answers
What are the main SQL numeric families? Exact integers, exact fixed-point decimals, and approximate floating-point types.
What do precision and scale mean? Precision is the total number of decimal digits; scale is the number to the right of the decimal point.
Why should FLOAT usually not store money? Binary floating point cannot represent many decimal fractions exactly, which can create reconciliation and equality problems.
When is BIGINT appropriate? When the required whole-number range or credible future growth exceeds a smaller integer type.
Why should a phone number not be numeric? It is an identifier with formatting and possible leading zeros, not a quantity used in arithmetic.
What is integer division? Division whose operand and result rules may discard a fractional part; convert to a decimal type when a fractional result is required.
Does DECIMAL guarantee correct financial logic? It provides exact decimal storage within its limits, but the application still needs correct precision, calculation sequence, rounding, currency, and constraints.
Why must application mappings be reviewed? A database value can lose exactness or range when converted to a narrower or approximate language or JSON type.
Key Takeaway
Numeric type selection is a choice about meaning and behavior, not merely storage. Integers provide exact whole numbers, decimal types provide exact values within declared precision and scale, and floating-point types provide wide approximate range.
NUMERIC TYPE CHOICE =
DOMAIN MEANING + EXACTNESS + RANGE + SCALE
+ CALCULATION RULES + APPLICATION COMPATIBILITY
Start with the business domain, plan for realistic growth, define NULL and rounding semantics, add constraints, test arithmetic boundaries, and verify the complete path through database, driver, application, and API. That approach produces numeric data that remains trustworthy as the system grows.