SQL Integer Types

Introduction

Integer types are SQL numeric types used to store exact whole numbers. They are appropriate for counts, quantities without fractions, sequence values, status codes, and many primary and foreign keys. Their main design question is range: how small or large can the value become throughout the system's life?

Choosing an integer type affects correctness, row and index size, joins, application mappings, API contracts, and migration risk. The right choice balances credible growth against unnecessary width without treating every digit-only value as a number.

What Is an Integer?

An integer is a whole number with no fractional part. It can be negative, zero, or positive:

-25
0
42
9000000

Values such as 12.5 are not integers. SQL integer types represent whole numbers exactly within a supported range.

Integer Types in SQL

The standard-style integer family contains SMALLINT, INTEGER or INT, and BIGINT. Database products may add types such as TINYINT or MEDIUMINT, use general decimal types for integers, or apply dynamic typing.

The names communicate relative capacity, but exact range, signedness, storage, and identity behavior are product-specific.

Why There Are Multiple Integer Types

A product rating, warehouse quantity, employee count, and global event identifier can have very different maximum values. Multiple widths let a schema choose enough range while controlling storage and index cost.

The smallest current sample is not enough. Selection should consider growth rate, retention, distributed allocation, imports, arithmetic, aggregate totals, and external-system ranges.

Main Integer Type Hierarchy

TypeCommon implementationCommon signed rangeTypical use
SMALLINT2 bytes-32,768 to 32,767Small bounded counts or codes
INTEGER4 bytes-2,147,483,648 to 2,147,483,647General whole-number data
BIGINT8 bytesApproximately -9.22 to 9.22 quintillionHigh-growth IDs and counters

These are widespread implementations, not a promise for every SQL product. Verify the database documentation before relying on exact limits.

SMALLINT

SMALLINT stores a relatively narrow whole-number range. It can be useful for values that are naturally bounded, such as a small rating scale, month number, limited retry count, or compact dimension code.

priority_level SMALLINT
    CHECK (priority_level BETWEEN 1 AND 5)

The type range alone does not enforce the business rule. Without the check constraint, the column can accept many values outside 1 through 5.

When SMALLINT Is Risky

SMALLINT is a poor choice for a growing identifier, cumulative event count, long-lived sequence, or any field whose maximum is uncertain. Saving a small amount of space is not worth an emergency production migration near range exhaustion.

INTEGER and INT

INTEGER is the common general-purpose whole-number type. INT is widely supported as a synonym.

stock_quantity INTEGER NOT NULL DEFAULT 0

employee_count INT

It commonly uses four bytes and offers a range suitable for many quantities, counters, and medium-growth keys. Do not assume the exact implementation without checking the target product.

INTEGER Use Cases

Good uses include inventory quantities, attempt counts, ranking positions, calendar years, and moderate-scale surrogate keys. Each still needs domain analysis. An inventory model may allow negative values for backorders, while another must enforce nonnegative quantities.

quantity INTEGER NOT NULL
    CHECK (quantity >= 0)

BIGINT

BIGINT supports a much larger exact whole-number range. It is appropriate for high-volume event IDs, distributed systems, long-retention audit rows, large counters, and references to external systems that already use 64-bit values.

event_id BIGINT GENERATED ALWAYS AS IDENTITY

Its range is large but not infinite. Capacity monitoring is still required for rapidly allocated or partitioned sequences.

SMALLINT Versus INTEGER Versus BIGINT

The correct choice depends on the domain and expected lifetime. SMALLINT is compact but limited. INTEGER balances range and size for general use. BIGINT protects high-growth values but widens every index, foreign key, join key, cache entry, and payload that carries it.

All Integer Types Are Exact

Within their range, integer values are exact. The database does not approximate 100 as a nearby value. This makes integers suitable for counts, discrete quantities, and identity values.

Exactness does not prevent overflow or integer division. Operations must remain within supported ranges and use an appropriate result type.

Integer Versus DECIMAL

Use an integer when fractional values are invalid. Use DECIMAL or NUMERIC when exact fractional values are required.

package_count INTEGER
unit_price DECIMAL(19, 4)

An integer cannot store 12.75 without changing the representation, such as storing minor units. A decimal declaration can preserve a defined scale directly.

Integer Versus Floating Point

Both integers and floating-point values can participate in arithmetic, but they serve different domains. Integers exactly represent discrete whole values. Floating point approximately represents measurements over a wide magnitude range.

Do not use floating point for identifiers or exact counters. Do not use an integer for measurements that legitimately require fractions unless a documented scaling model converts them to whole units.

Integer Range and Overflow

Overflow occurs when a value or result exceeds the type's maximum or minimum. A write can fail even if the application language still represents the value.

current_count + increment_value

Both operands can fit individually while their sum does not. The same risk applies to subtraction near the negative minimum and multiplication of moderate operands.

Arithmetic Overflow and Casting

Casting before arithmetic can provide a wider result domain:

CAST(quantity AS BIGINT) * unit_count

Casting after an overflowing calculation is too late if the original expression is evaluated using the narrow type. Result-type rules are database-specific, so test the target database and use documented widening behavior.

Choosing the Safest Width

Estimate values using maximum creation rate multiplied by retention time and expected growth, then add margin for imports, retries, replicas, and unexpected expansion. A key expected to receive 100 million rows per year needs a different plan from a status code limited to ten values.

Choose the smallest type that safely covers credible future demand, not merely today's data.

Why Not Use BIGINT Everywhere?

Wider values consume more storage in tables and every associated index. Primary keys are repeated in foreign keys and may be included in secondary index entries depending on the database. Wider keys reduce the number of entries per page and can increase memory and I/O.

These costs may be modest for small systems, while range exhaustion can be severe. The decision should follow scale and lifetime requirements rather than a universal rule.

Integer Primary Keys

Integer surrogate keys are popular because they are compact, exact, sortable, and efficient for comparisons and indexes.

customer_id BIGINT GENERATED ALWAYS AS IDENTITY
    PRIMARY KEY

A surrogate key identifies the row but does not replace unique constraints on real business identifiers such as account numbers or external references.

Foreign Key Types Must Match

customers.customer_id BIGINT
orders.customer_id BIGINT

Parent and child key columns should have compatible type, width, and signedness. A mismatch can block constraint creation, cause implicit conversions, complicate joins, or truncate application values. Changing a primary key type later requires coordinated changes across all references.

Auto-Generated Integer IDs

Databases provide mechanisms that generate integer values automatically. Standard identity syntax and product features such as MySQL AUTO_INCREMENT, SQL Server IDENTITY, PostgreSQL identity columns, and Oracle identity or sequences are common examples.

Generation and type are separate concerns. An integer column does not become auto-generated automatically, and an identity property does not change the type's maximum range.

Identity Values Are Not Gapless

Generated values can have gaps because of rollbacks, failed inserts, caching, concurrent allocation, restarts, replication, or manual changes. A gap does not mean a row was deleted or data was lost.

Do not use a technical identity sequence for legally gapless invoice or receipt numbering. Such requirements need a dedicated, transactionally designed business process.

Signed Integers

Signed integer types represent both negative and nonnegative values. Negative numbers can be meaningful for adjustments, temperature, elevation, coordinate offsets, accounting deltas, or inventory corrections.

If the domain forbids negatives, a check constraint communicates that rule more clearly than relying only on application validation.

Unsigned Integers

MySQL provides unsigned integer variants. Removing the negative range allows a larger positive maximum for the same storage width. This is useful in some designs but is not portable to PostgreSQL or SQL Server as a general integer feature.

A nonnegative check on a signed column enforces a business rule but does not increase its maximum. Signedness must also match across primary and foreign keys in systems that support unsigned types.

MySQL Integer Types

MySQL provides TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, with signed and unsigned forms. The display-width syntax historically associated with integer declarations does not increase storage or range and should not be confused with numeric capacity.

TINYINT(1) has often been used for Boolean-like data, but it remains an integer type. Use clear constraints and application mappings.

SQL Server Integer Types

SQL Server provides tinyint, smallint, int, and bigint. Its tinyint is unsigned, while the wider integer types are signed. Identity generation is configured separately through the IDENTITY property.

SQL Server also provides COUNT_BIG for counts that exceed the return capacity of ordinary COUNT.

PostgreSQL Integer Types

PostgreSQL provides smallint, integer, and bigint. Historical smallserial, serial, and bigserial conveniences create supporting sequence behavior, while modern designs can use SQL identity columns.

These types are signed. A nonnegative business domain can be enforced with a check constraint.

Oracle Integer Modeling

Oracle commonly models whole numbers with NUMBER declarations. INTEGER is related to a decimal numeric definition with zero scale rather than a separate fixed-width binary type matching every other product.

Use explicit precision where the domain requires a bounded number of digits, and review Oracle-specific identity and sequence behavior.

SQLite Integer Model

SQLite uses dynamic typing and an INTEGER storage class capable of storing signed values in varying byte widths up to its supported 64-bit range. Column affinity does not behave like rigid server-database integer declarations.

An INTEGER PRIMARY KEY has a special relationship to the row identifier. SQLite's exact declaration and generation semantics should be reviewed before porting assumptions from another database.

Cross-Database Portability

SMALLINT, INTEGER, and BIGINT communicate broadly portable intentions, but implementation details remain different. TINYINT, MEDIUMINT, unsigned variants, sequence syntax, identity rules, and overflow handling are more dialect-specific.

A migration must verify ranges, signedness, generated values, default expressions, foreign keys, driver mappings, and existing data near boundaries.

Integer Arithmetic

quantity + received_quantity
quantity - shipped_quantity
unit_count * package_count

Addition, subtraction, and multiplication return whole-number results when integer rules apply. Intermediate overflow is possible. If a calculation can exceed the input type, widen it before the operation.

Integer Division

5 / 2

Several databases return an integer-like result for integer division, while exact behavior varies. Do not assume 2.5. Convert an operand when a fractional result is required:

CAST(numerator AS DECIMAL(20, 6))
    / NULLIF(denominator, 0)

Modulo Operations

Modulo returns the remainder after division. Syntax differs, with percent notation and MOD() commonly available.

WHERE sequence_number % 2 = 0

This tests for an even value in supporting dialects. Negative operands can have product-specific remainder-sign behavior, so test when negatives are relevant.

Integer Comparisons and Filters

WHERE quantity > 0
  AND priority_level BETWEEN 1 AND 5
  AND department_id IN (10, 20, 30)

Integer comparisons support equality, range, membership, ordering, joins, and indexed searches. Parameter types should match the column to avoid implicit conversion.

NULL Is Not Zero

NULL means unknown, missing, or not applicable. Zero is a known integer. A missing review score and a score of zero are different facts.

Do not use zero as a fake null unless zero is impossible and the convention is unavoidable. Nullable columns or explicit status modeling communicate meaning more reliably.

Integer Constraints

retry_count INTEGER NOT NULL DEFAULT 0
    CHECK (retry_count BETWEEN 0 AND 10),

age_years SMALLINT
    CHECK (age_years BETWEEN 0 AND 130)

The integer type enforces whole numbers within a technical range. NOT NULL, DEFAULT, and CHECK express business validity. A default is appropriate only when the omitted value truly has a known initial meaning.

Year as an Integer

A standalone calendar year can reasonably be an integer with a supported range. A complete date, month boundary, timestamp, or duration should use temporal types. Storing every date component as separate integers makes validation and date operations more difficult.

Boolean Versus Integer Flags

Databases with a Boolean type should generally use it for true/false domains. Other systems commonly use 0 and 1 in a small integer column.

is_active SMALLINT NOT NULL
    CHECK (is_active IN (0, 1))

Without a constraint, an integer flag can contain values such as 2 or -1, creating ambiguous application behavior.

Status Codes as Integers

Integer status codes can be compact but hide meaning in queries and logs. A lookup table can document allowed values and enforce references:

status_id SMALLINT NOT NULL
    REFERENCES order_statuses(status_id)

Character codes or native enums can be clearer in some systems. Choose based on stability, portability, readability, and governance rather than storage alone.

Digits That Should Remain Text

Phone numbers, postal codes, account references, product codes, and identifiers with leading zeros should usually be character data. They are compared as labels and may contain prefixes, formatting characters, or country-specific lengths.

Arithmetic on these values is meaningless. Numeric storage would discard formatting and impose an irrelevant range.

Integer Sorting and Aggregation

Integer columns sort by numeric magnitude, so 2 comes before 10. Text columns can sort lexically, placing '10' before '2'.

SUM, AVG, MIN, MAX, and COUNT produce numeric results. Aggregate return types are database-specific. A sum or count can exceed the source column's range, so verify the documented result type for high-volume workloads.

Integer Indexes

Integer keys are index-friendly because their representation is fixed, comparisons are straightforward, and values are compact relative to many text identifiers. Narrower indexes generally fit more entries per page.

Index benefit still depends on selectivity, query shape, table size, clustering, and returned columns. An integer predicate is not automatically fast without a suitable access path.

Integer Joins and Type Mismatch

JOIN orders AS o
  ON o.customer_id = c.customer_id

Matching integer types let the database compare keys directly. Joining INTEGER to character text or signed to unsigned values can require conversion, reject invalid values, or reduce index use. Align schema types rather than casting every query.

Explicit and Implicit Conversion

CAST(text_value AS INTEGER)

CAST(quantity AS BIGINT)

Text-to-integer conversion can fail on invalid characters or out-of-range values. Decimal-to-integer conversion may round, truncate, or reject according to product and function. Integer-to-decimal conversion is exact when the target precision is sufficient.

Explicit conversion makes intent visible, but clean source types are preferable to repeated runtime conversion.

Integer Literals

A literal such as 42 is normally assigned an integer-compatible type by the parser. Very large literals may be assigned a wider exact numeric type or rejected depending on the database.

Cast important constants when result width matters, especially before multiplication or aggregation.

Java Integer Mapping

Java commonly maps SQL small and ordinary integers to short or int and BIGINT to long, depending on the driver and ORM. Java primitive types cannot represent NULL, so nullable columns require wrappers such as Integer or Long.

Validate both range and nullability. An SQL BIGINT does not fit in Java int.

Python Integer Mapping

Python integers can grow beyond fixed machine ranges, but the database column remains bounded. A Python value must still be validated before insertion. Drivers may return None for SQL NULL.

JavaScript, JSON, and BIGINT

JavaScript's ordinary Number cannot exactly represent every signed 64-bit integer. A large database ID can therefore change when parsed from JSON as a number.

APIs often serialize large IDs as strings, use BigInt-aware libraries, or constrain values to a universally safe range. Document the contract and test browser, mobile, analytics, and reporting consumers.

ORM Integer Mappings

An ORM model can map a language integer property to different SQL widths depending on provider conventions. Review generated migrations rather than assuming that int, long, or an identity annotation creates the intended database type.

Changing a model from 32-bit to 64-bit may require an explicit database migration for primary keys, foreign keys, sequences, and indexes.

Sequence Exhaustion

Monitor identity and sequence utilization before the maximum approaches. Calculate remaining capacity from current high value and allocation rate, accounting for gaps and non-production environments if they share ranges.

Do not wait until inserts fail. Widening a key in a large system can require long-running table rewrites, index rebuilds, replication coordination, API changes, and downtime planning.

Sequence Start, Increment, and Caching

Sequence and identity facilities often support a starting value, increment, and cache behavior. A nondefault start can reserve legacy ranges, while a larger increment can support allocation patterns such as separate odd and even values. These settings do not change the column's maximum.

Caching improves allocation throughput by reserving values in memory, but unused cached values can become gaps after a restart. Configure generation for performance and uniqueness, not for an assumption that every integer will appear.

Distributed Integer ID Allocation

Multiple writers or regions need a strategy that prevents duplicate IDs without creating an unacceptable coordination bottleneck. Options include a central database sequence, nonoverlapping numeric ranges, worker-encoded 64-bit IDs, or an external allocation service.

Worker-encoded IDs can pack time, region, worker, and sequence components into one BIGINT. They provide decentralized allocation but introduce limits for clock handling, worker count, per-time-unit throughput, and lifespan. Document the bit layout and monitor every component's capacity.

Integer Type Migration

A migration from INTEGER to BIGINT affects the parent column, every foreign key, relevant indexes, sequences or identities, stored routines, application models, and API schemas. Some databases can perform parts online; others require locks or table rebuilds.

Plan the migration in phases, test realistic data volume, maintain referential consistency, and monitor replication and rollback capability.

Integer Partition Keys

Integer ranges can partition large tables by tenant, event range, or generated bucket. Partitioning should align with pruning predicates, data lifecycle, and maintenance operations.

A sequential ID is not automatically a good time partition key. ID allocation can be cached, distributed, imported, or generated out of chronological order.

An ID Is Not a Timestamp or Count

A larger ID often appears newer in simple systems, but that ordering is not guaranteed by the relational model. Sequence caching, concurrent transactions, rollbacks, replication, and imported rows break the assumption.

Similarly, the maximum ID is not the row count because values can be skipped or rows deleted. Store timestamps for time and use COUNT for counts.

Identifiers and Security

Sequential integer IDs are easy to enumerate. That is not a defect when authorization is correct, but exposing them can reveal approximate volume or make resource discovery easier. Every request must verify that the authenticated principal can access the referenced row.

If public unpredictability is required, use a separate public identifier or another suitable key strategy. Obscure IDs do not replace object-level authorization.

Transactional Counters

A counter updated by many sessions can become a contention point. This statement is atomic within the database operation, but concurrency and isolation still matter:

UPDATE page_statistics
SET view_count = view_count + 1
WHERE page_id = :page_id;

High-frequency counters may require partitioned counters, append-only events, asynchronous aggregation, or database-specific atomic features. Also plan for arithmetic overflow and decide whether occasional delay or approximation is acceptable.

Do Not Reuse Deleted IDs Casually

Reusing an old surrogate key can confuse caches, audit logs, external references, event streams, and historical reports. Generated technical identifiers are normally treated as permanently unique within their scope, even after deletion.

Sequential and Random Integer IDs

Sequential keys usually provide compact indexes and append-friendly insertion patterns. Randomly distributed numeric identifiers can reduce predictability or support distributed allocation but may increase index fragmentation and require a larger range.

Security should not rely on IDs being hard to guess. Authorization must validate every resource request regardless of identifier pattern.

Integer Business Keys

Some whole numbers are genuine business identifiers, such as an externally assigned numeric code. They can be keys if stable, unique, non-null, and governed by the domain. Many teams still add a surrogate key while enforcing the business number with a unique constraint.

Common Integer Design Mistakes

  • Using SMALLINT for a sequence that will keep growing.
  • Using BIGINT for every column without considering index cost.
  • Storing phone numbers, postal codes, or formatted IDs as integers.
  • Using an integer for money without a documented minor-unit model.
  • Expecting integers to store fractional measurements.
  • Using zero instead of NULL for unknown values.
  • Omitting range and allowed-value constraints.
  • Assuming auto-generated values are gapless or begin at one.
  • Assuming a larger ID always means a newer row.
  • Using different widths or signedness for primary and foreign keys.
  • Ignoring application-language and JSON range limits.
  • Ignoring aggregate and arithmetic overflow.
  • Storing genuine numeric quantities as text.

Integer Type Decision Process

  1. Confirm that the value is a whole-number quantity or numeric key.
  2. Determine whether negatives, zero, and NULL are valid.
  3. Estimate minimum, maximum, growth rate, and retention.
  4. Consider arithmetic and aggregate result ranges.
  5. Choose a width with credible future margin.
  6. Match all primary and foreign key definitions.
  7. Add constraints for the business domain.
  8. Verify identity, sequence, signedness, and portability rules.
  9. Review driver, ORM, language, JSON, and API mappings.
  10. Monitor capacity and test boundary behavior.

Example Employee Table

CREATE TABLE employees (
    employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    department_id INTEGER NOT NULL,
    years_experience SMALLINT,
    review_score SMALLINT,
    CHECK (years_experience IS NULL
           OR years_experience BETWEEN 0 AND 80),
    CHECK (review_score IS NULL
           OR review_score BETWEEN 1 AND 5)
);

The key allows substantial growth, while the naturally bounded fields use smaller types and explicit business constraints.

Example Inventory Table

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    quantity_on_hand INTEGER NOT NULL DEFAULT 0,
    reorder_level INTEGER NOT NULL DEFAULT 0,
    reserved_quantity INTEGER NOT NULL DEFAULT 0,
    CHECK (quantity_on_hand >= 0),
    CHECK (reorder_level >= 0),
    CHECK (reserved_quantity BETWEEN 0 AND quantity_on_hand)
);

The constraints communicate a model that forbids negative stock and reservations beyond available quantity. A backorder-based business would use different rules.

Example Order Tables

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_status_id SMALLINT NOT NULL,
    item_count INTEGER NOT NULL,
    CHECK (item_count >= 0)
);

CREATE TABLE order_items (
    order_item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0)
);

The parent and child order key types match, while quantity rules prevent meaningless zero or negative order lines.

Capacity Planning Example

Suppose a table receives 20 million rows per month and retains data for ten years. That is roughly 2.4 billion rows before considering retries, gaps, imports, or growth. A common signed 32-bit integer range is already insufficient.

This simple estimate supports choosing BIGINT before deployment. Capacity calculations should use peak and growth scenarios, not only current averages.

Operational Capacity Monitoring

Track the current sequence or identity high-water mark, maximum supported value, allocation rate, and estimated time remaining. Alert while there is enough time to design, test, and deploy a migration.

Monitoring must account for gaps and preallocated ranges rather than relying only on row count. A table can contain few rows while its generator is near exhaustion because values were skipped, deleted, imported, or allocated in large blocks.

Integer Testing Checklist

  • Test zero, negative values, and NULL according to the domain.
  • Test exact technical and business boundaries.
  • Test values just below and above every allowed boundary.
  • Test addition, subtraction, multiplication, division, and aggregates near limits.
  • Test identity gaps, rollbacks, and concurrent inserts.
  • Test PK/FK compatibility and migration scripts.
  • Test serialization of large values through APIs and JavaScript clients.
  • Test invalid text and decimal conversions.
  • Monitor sequence growth and alert well before exhaustion.

Interview Questions and Answers

What are the primary SQL integer types? SMALLINT, INTEGER or INT, and BIGINT, with additional product-specific types.

How do they differ? Mainly by supported range and storage width; exact details depend on the database.

Are integer values exact? Yes, within their supported range.

Why not use BIGINT everywhere? Wider keys and values increase table, index, cache, join, and transport costs.

Why must PK and FK types match? Compatible types avoid conversion, range, signedness, constraint, and performance problems.

Are identity values gapless? No. Rollbacks, failures, caching, and concurrency can create gaps.

What is integer division? Division governed by integer operand rules that may discard a fractional result; cast when a decimal result is needed.

Why can BIGINT be unsafe in JavaScript? Ordinary JavaScript numbers cannot exactly represent every 64-bit integer.

Key Takeaway

SQL integer types store exact whole numbers, but choosing among them requires more than selecting the smallest current range. Model the value's meaning, growth, signedness, nullability, arithmetic, keys, application mappings, and portability.

INTEGER TYPE CHOICE =
WHOLE-NUMBER DOMAIN + RANGE + GROWTH
+ STORAGE IMPACT + CROSS-SYSTEM COMPATIBILITY

Use constraints for business validity, keep related key types aligned, monitor generated sequences, and test every boundary from database storage through API consumers. That discipline prevents overflow, incorrect identifiers, and costly emergency migrations.