skip to content

How do you choose between SMALLINT, INTEGER and BIGINT, and what happens on overflow?

level: middleimportance: should knowfreq 45%

answer

  1. The three types differ in one property only
  2. Think about the largest value ever, not today's
  3. Roughly two billion is the middle boundary
  4. SQL does not behave like C here
  5. The failure mode is rejected inserts

basics

~20 s

They are the same exact integer type at three widths — in mainstream engines 16, 32 and 64 bits — so pick the narrowest that comfortably covers the value's whole lifetime range. Exceeding it raises an out-of-range error; SQL does not wrap around.

solid answer

~50 s

`SMALLINT`, `INTEGER` and `BIGINT` are exact integer types differing only in the range they can hold. The standard requires each to be at least as wide as the previous one and leaves the exact precision implementation-defined, but every mainstream engine uses signed 16-, 32- and 64-bit values: roughly ±32 thousand, ±2.1 billion and ±9.2 quintillion. Choose by the *maximum value over the column's lifetime*, not today's data: `INTEGER` is the sensible default for counts and moderate keys, `BIGINT` for surrogate keys on high-volume tables, event ids, byte counts and anything that might plausibly pass two billion. `SMALLINT` is worth it only where the domain is genuinely tiny and fixed. Crucially, SQL does not wrap on overflow the way C does — an assignment or arithmetic result outside the range raises an error, so a table that outgrows `INTEGER` starts rejecting inserts rather than silently writing negative ids.

code

sql · 10 lines
sql
CREATE TABLE shipment (
  shipment_id  BIGINT       NOT NULL,  -- grows fast: 64-bit from day one
  carton_count INTEGER      NOT NULL,  -- ordinary count
  service_year SMALLINT     NOT NULL,  -- small fixed domain
  account_no   VARCHAR(20)  NOT NULL   -- looks numeric, has leading zeros
);

-- Widen before the arithmetic, not after, when a sum could exceed 32 bits
SELECT SUM(CAST(carton_count AS BIGINT)) AS total_cartons
FROM   shipment;

go deeper

for a junior

Know that the three types are the same kind of number at different widths and that INTEGER runs out a little past two billion. Being able to name BIGINT as the wider choice is the expected answer.

for a middle

Explain sizing by lifetime maximum rather than current data, that overflow raises an error rather than wrapping, and why a surrogate key on a fast-growing table should be BIGINT from day one.

for a senior

Show that you have seen the failure: an INTEGER key exhausting its range means every insert starts failing, and the fix is a schema change on the largest table you own, so you plan the width up front and monitor the approach.

for a principal

Own the standard across a schema — the default key width, where narrower types are permitted, and the recognition that identifiers issued by other systems are not integers at all and should not be typed as if they were.

## One type, three widths Standard SQL's exact integer types are `SMALLINT`, `INTEGER` (spelled `INT`) and `BIGINT`. Semantically they are identical: whole numbers, exact arithmetic, no fractional part. They differ only in the range of values they can hold. The standard states the precisions are implementation-defined, with the constraint that `SMALLINT` is no wider than `INTEGER`, which is no wider than `BIGINT`. In practice every mainstream engine uses signed two's-complement integers: - `SMALLINT` — 16 bits, -32,768 to 32,767 - `INTEGER` — 32 bits, -2,147,483,648 to 2,147,483,647 - `BIGINT` — 64 bits, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Note what is *not* standard: `TINYINT` is a vendor extension, not an ISO type, and unsigned variants are likewise an extension offered by some engines only. If portability matters, stay with the three standard names and assume signed. ## Choosing a width Size the column by the largest value it must hold across its whole lifetime, then add headroom for growth you have not predicted: - **Surrogate keys on tables that will grow** — `BIGINT`. Two billion sounds enormous until you count rows that are inserted and deleted, since key values are consumed and not reused. Event logs, message tables, ledger lines and anything fed by an ingestion pipeline should start at `BIGINT`. - **Ordinary counts and moderate references** — `INTEGER`. A per-user counter, a page count, a reference to a table of customers. - **Genuinely tiny fixed domains** — `SMALLINT` for a year, a rating out of 100, a small code. The saving is real only in aggregate, and buying it costs you the ability to grow the domain later. - **Beyond 64 bits** — use `DECIMAL(p, 0)`, which is an exact integer with as many digits as you declare. It is the portable way to hold, say, a 30-digit external identifier. A related warning: an external identifier that merely *looks* numeric — an account number with leading zeros, a code that may gain a letter next year — is not an integer at all. Store it as a character type, since arithmetic on it is meaningless and integer storage destroys leading zeros. ## Overflow raises an error This is where the type diverges sharply from most programming languages. In C-family languages a 32-bit counter that passes its maximum wraps around to a large negative number, silently. SQL's exact numeric types are defined to raise an error instead: an assignment or an arithmetic result outside the declared range produces `numeric value out of range` (or the engine's spelling of it) and the statement fails. That is the safe behaviour, but it has a sharp operational edge. A table whose `INTEGER` key generator reaches 2,147,483,647 does not corrupt data — it starts **rejecting every insert**, immediately, at whatever hour that happens. Widening the column afterwards is a schema change on a very large table, which is the worst moment to be discovering it. This is why experienced practitioners default surrogate keys to `BIGINT`: the extra bytes per row are cheap compared with an unplanned emergency migration. Overflow can also strike in the middle of an expression rather than at storage. Summing many `INTEGER` values, or multiplying two large ones, can exceed the integer range even though every input and the intended result fit fine. Where that is plausible, cast an operand to a wider type before the arithmetic rather than after it. ## Booleans and flags A related sizing question: how to store a true/false flag. Standard SQL has `BOOLEAN`, with the values `TRUE`, `FALSE` and the unknown state that `NULL` carries. Support is uneven — some engines expose it natively, others map it onto a small integer, and SQL Server uses `BIT`. Where `BOOLEAN` exists, use it: it says exactly what the column means. Where it does not, a `SMALLINT` or a one-character column constrained by a `CHECK` to the two intended values is the portable fallback, and the `CHECK` is what stops the column drifting into a general-purpose status field. ## The short answer Same type at three widths; choose by lifetime maximum plus headroom; default keys on growing tables to `BIGINT`; and remember that overflow is a hard error, not a wraparound.

  • An INTEGER surrogate key on a busy table is approaching two billion. What actually happens when it arrives, and what does that imply?
    The next value falls outside the 32-bit range, so the insert raises an out-of-range error and keeps doing so — the table stops accepting writes rather than corrupting anything. The implication is planning: widening the key column on a very large live table is a substantial change, so surrogate keys on tables expected to grow should be BIGINT from the start.
  • How would you store a true/false flag portably when the engine has no BOOLEAN type?
    Use a narrow integer or a single-character column and add a CHECK constraint limiting it to the two intended values, so the column cannot quietly become a general status field. Where BOOLEAN is available, prefer it — it documents intent, and NULL already provides the unknown state without a third magic value.

saying these in an interview costs you the question

  • Expects integer overflow to wrap around to a negative value
  • Picks SMALLINT to save space on a column that may grow
  • Stores an external account number with leading zeros as an integer
  • Believes TINYINT and UNSIGNED are standard SQL types
  • Assumes an expression cannot overflow if the final result fits

context

resolving links…