Choosing Correct Data Types in SQL

Introduction

Choosing a SQL data type means selecting the representation that best matches a column's business meaning, valid range, precision, storage needs, query patterns, constraints, and application behavior. It is a modeling decision, not merely a choice about how many bytes to reserve.

A poor type can permit invalid data, lose precision, overflow, enlarge indexes, force runtime conversion, complicate application code, and make future migrations risky. The core principle is to choose from what the data means rather than what its current characters resemble. A ZIP code such as 00501 contains digits but is an identifier, not a quantity, so text preserves its leading zero and intended operations.

Data Types Are Part of the Model

A type communicates which values are possible and which operations make sense. INTEGER suggests arithmetic and range comparison. DATE suggests calendar operations. BOOLEAN suggests a logical proposition. JSON suggests a semi-structured document. The declaration becomes executable documentation shared by the database, drivers, tools, and developers.

Types alone do not express every business rule. An INTEGER can hold a negative quantity, and a VARCHAR can hold malformed email text. Good design combines the narrowest truthful type with NOT NULL, CHECK, UNIQUE, foreign keys, defaults, and application validation.

Start with Business Meaning

Before choosing INT or VARCHAR, ask what the column represents. A phone number is dialed and formatted, not added. A product code can contain leading zeros and letters. A monetary amount needs exact decimal arithmetic. A birth date is a calendar date, while an account creation time is an event timestamp.

SampleMeaningLikely family
100Inventory quantityInteger
100.25PriceExact decimal
00501Postal codeCharacter
8475551234Phone numberCharacter
2026-10-31Calendar dateDATE
trueEnabled stateBoolean

Identifier Versus Quantity

A useful test is whether arithmetic makes business sense. Adding two order quantities can be meaningful; adding two phone numbers cannot. Numeric-looking identifiers often belong in text or a native identifier type, while quantities belong in numeric types.

Identifiers may require leading zeros, punctuation, check digits, fixed formatting, or values beyond application numeric precision. Treating them as numbers can strip presentation-significant characters and invite meaningless calculations. Conversely, storing a real quantity as text breaks numeric ordering and requires conversion for every calculation.

Main SQL Type Families

FamilyRepresentsExamples
IntegerWhole-number quantities and numeric keysSMALLINT, INTEGER, BIGINT
Exact numericPrecise fractional valuesDECIMAL, NUMERIC
Approximate numericScientific magnitude and measurementREAL, FLOAT, DOUBLE
CharacterText and textual identifiersCHAR, VARCHAR, TEXT
TemporalDates, times, instants, durationsDATE, TIME, TIMESTAMP, INTERVAL
Logical or domainFlags and controlled valuesBOOLEAN, ENUM, constrained text
BinaryRaw byte sequencesBINARY, VARBINARY, BLOB, BYTEA
Semi-structuredFlexible nested documentsJSON, JSONB, XML

Choosing Integer Types

Use integer types for whole-number values where fractions are invalid. Choose SMALLINT, INTEGER, or BIGINT from expected range, growth rate, and platform limits. A rating from 1 through 5 needs little capacity; a global event identifier may eventually require BIGINT.

Do not choose BIGINT everywhere merely because it seems future-proof. Wider keys enlarge primary and secondary indexes, foreign keys, caches, and network payloads. Do not under-size identity columns either: widening a heavily referenced key later can be expensive. Estimate peak creation rate multiplied by system lifetime and safety margin.

The storage range is not the business range. A SMALLINT can hold values far outside a valid percentage or rating, so add a CHECK constraint.

rating SMALLINT NOT NULL
  CHECK (rating BETWEEN 1 AND 5)

Choosing Exact Decimal Types

DECIMAL or NUMERIC stores exact base-10 values and is the usual choice for money, rates, tax, and contractual quantities. In DECIMAL(p, s), precision p is the total significant digits and scale s is the fractional digits.

unit_price DECIMAL(12, 2) NOT NULL
  CHECK (unit_price >= 0)

Plan both sides of the decimal point from the largest supported value and required fractional precision. Not every currency uses two decimal places, and financial calculations may require extra intermediate scale. Specify rounding policy in the application and reporting layers.

FLOAT and DOUBLE

Floating-point types are approximate binary numbers. They cover large ranges efficiently and suit scientific measurements, simulations, and coordinates where small representation error is acceptable. They are poor choices for exact monetary equality because many decimal fractions cannot be represented exactly.

Use DOUBLE when more approximate precision is needed than REAL or single precision. Do not test floating-point results for exact decimal equality without a domain-aware tolerance. When exact digits are the requirement, choose DECIMAL instead.

Numeric Selection Questions

  1. Can the value have a fractional part?
  2. Must decimal arithmetic be exact?
  3. What are the true minimum and maximum values?
  4. How many fractional digits must survive storage and calculation?
  5. What rounding and overflow behavior is required?
  6. Does the application language preserve the same precision?

Choosing CHAR, VARCHAR, and TEXT

Use character types for human-readable text and identifiers whose digits are not quantities. CHAR is reasonable for truly fixed-length values such as a two-letter country code or a standardized fixed code. VARCHAR suits names, email addresses, URLs, and descriptions whose actual length varies.

A known maximum does not make text fixed length. A 100-character name column should normally be VARCHAR rather than CHAR(100), because names do not all occupy 100 characters. TEXT or CLOB suits large content, but using an unlimited type for every string can restrict indexing, constraints, ORM behavior, and memory planning.

Choosing Character Length

Length should come from the domain, protocol, or accepted product requirement, not the longest current sample. Real names, addresses, and URLs vary more than test data. Character limits can be measured in characters or bytes depending on the engine and declaration.

Unicode characters may require multiple bytes. Validate international names, combining marks, emoji where allowed, and normalization behavior. Do not design from English-only examples. Decide whether the limit is a storage safeguard, a business rule, or a user-interface preference; those are not always the same.

Character Sets and Collations

The type family does not fully define text behavior. Character set controls representable characters, while collation controls comparison and sorting. Case-insensitive email lookup, case-sensitive API keys, and locale-aware names may need different semantics.

Columns used in joins and foreign keys should have compatible type, length, character set, and collation. Hidden conversion can prevent index use or change equality. Security-sensitive tokens and identifiers should use deliberate binary or case-sensitive comparison.

Prefer Native Semantic Types

If the database provides a trustworthy native type for UUID, network address, spatial geometry, JSON, date, or another domain, it can offer validation, compact storage, operators, and indexes that generic VARCHAR lacks. Native types also communicate intent.

The tradeoff is portability and driver support. A multi-database product may prefer a portable representation, while a single-platform system can benefit from mature native features. Make that decision explicitly rather than defaulting to text.

Choosing DATE, TIME, and TIMESTAMP

Use DATE for a calendar date such as birth date, due date, or holiday when time of day is not part of the fact. Use TIME for a time-of-day value when it makes sense independently. Use a timestamp or datetime type for an event such as account creation or payment authorization.

Storing dates as VARCHAR permits invalid dates, produces lexical sorting problems, and complicates ranges and arithmetic. Storing a birth date as a timestamp invents a time and zone that do not belong to the domain. Select the temporal concept first, then the product-specific type.

Time Zones and Temporal Semantics

An event instant, a local appointment, and a recurring wall-clock schedule are different. Event instants can be stored in UTC or a type that preserves an offset, with the user's zone used for display. A future meeting at 9:00 in a named region may need the region identifier because daylight-saving rules can change the offset.

Database names are vendor-sensitive. SQL Server timestamp is historically a row-version binary value, not a date-time. Oracle DATE includes time-of-day fields. SQLite has flexible storage conventions. Verify range, precision, time-zone behavior, and driver mapping for the actual product.

Choosing Boolean Types

Use a Boolean for one proposition with two meaningful states, such as is_enabled. Decide whether NULL is allowed; a nullable Boolean creates a third unknown state. Products without native BOOLEAN may use BIT, constrained integers, or other conventions.

Do not use one Boolean for a workflow with pending, approved, rejected, and expired states. Use a controlled status. A timestamp can be better than a flag when the system needs to know whether and when an event occurred, such as verified_at.

Choosing ENUM, CHECK, or Lookup Tables

A small, stable, mutually exclusive domain can use native ENUM or constrained text. CHECK is often portable and easy for one column. A lookup table is stronger when values change dynamically, need translations or display order, carry metadata, or are referenced by several tables.

Status vocabulary and workflow transitions are separate concerns. A type restricts the allowed labels but does not determine which transition is legal from the current state.

Choosing Binary Types

Raw hashes, ciphertext, signatures, and file bytes belong in binary types rather than character columns. Use fixed BINARY for a known exact length, variable binary for bounded byte sequences, and BLOB or product-specific large-object facilities for substantial payloads.

Hexadecimal and Base64 are text encodings of bytes, not binary storage. Use them only when a text boundary requires them. Large files may belong in object storage with metadata, ownership, size, and hash retained in SQL; consider transactions, backups, replication, and recovery.

Choosing JSON

JSON suits flexible, sparse, nested secondary attributes whose shapes legitimately differ between rows. It is useful for product-specific properties, preferences, event payloads, and external snapshots. Native JSON is preferable to plain text when the database offers validation, path functions, and indexing.

Keep primary keys, foreign keys, prices, statuses, authorization boundaries, and common filters in relational columns. If many JSON paths need constraints, joins, and indexes, those properties may belong in normal columns or child tables. JSON is controlled flexibility, not a replacement for relational modeling.

Nullability Is Part of Type Design

Every column needs an explicit decision about absence. NULL is appropriate when a value can genuinely be unknown, not yet available, or not applicable. NOT NULL is appropriate for mandatory facts. Do not use zero, empty text, or fake dates merely to avoid NULL.

One NULL marker cannot explain every missing reason. Add a status or reason when unknown, not applicable, withheld, and not collected lead to different business behavior. Defaults supply omitted values but do not replace explicit NULL or prove that a value is correct.

Data Type Plus Constraints

A type defines a broad representation; constraints narrow it to the domain.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    sku VARCHAR(40) NOT NULL UNIQUE,
    unit_price DECIMAL(12, 2) NOT NULL
      CHECK (unit_price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0
      CHECK (stock_quantity >= 0),
    status VARCHAR(20) NOT NULL
      CHECK (status IN ('DRAFT', 'ACTIVE', 'RETIRED'))
);

The declarations communicate identity, exact money, whole quantity, nullability, defaults, uniqueness, and allowed states. Application validation improves user feedback, while database constraints protect every writer.

Types and Query Patterns

Correct types let the optimizer compare values directly and use indexes. Numeric text sorts lexically, so 100 can appear before 20. Date text may sort correctly only under one carefully enforced format. Repeated CAST expressions add CPU and can make predicates non-sargable.

Store the value in the type used by its dominant operations. If every query converts VARCHAR to DECIMAL before comparing price, the schema is fighting the workload. Clean and migrate the source rather than making conversion permanent query logic.

Index Width and Storage Cost

Narrow keys generally allow more index entries per page, improve cache density, and reduce I/O. A primary-key value can also appear in every secondary index and foreign key, multiplying its cost. Type width matters at scale.

Meaning and correctness take priority over small byte savings. A type too narrow for future IDs or too imprecise for finance creates much greater cost than a modestly wider correct representation. Measure realistic row counts, index definitions, and growth rather than optimizing isolated columns.

Primary and Foreign Key Compatibility

A foreign key should match its parent key's type and, for character keys, compatible length, character set, and collation. Mismatches can prevent constraints, trigger implicit conversion, or block index-efficient joins.

Choose between numeric identities, UUIDs, and natural keys based on generation, distribution, stability, disclosure, merge requirements, and index locality. Do not store UUIDs as generic VARCHAR by habit when a native UUID or BINARY(16) representation better fits and the application supports it.

Surrogate and Natural Keys

A natural key has business meaning, such as a standardized code. It can be effective when stable, compact, and governed. A surrogate key has no business meaning and protects relationships from changes to mutable natural attributes.

Using a surrogate does not remove the natural uniqueness rule. Keep a UNIQUE constraint on the business identifier where required. Avoid embedding volatile business meaning into generated IDs because later reorganizations make encoded parts misleading.

Units Belong in the Model

A numeric value without a unit is ambiguous. Store temperature, distance, duration, weight, and percentage under a documented unit convention or with an explicit unit code. The type must support the required range and precision after conversion.

Currency symbols are presentation, while currency codes are data. Multi-currency money usually needs amount DECIMAL plus a constrained ISO currency code. Duration can use a numeric value in one documented unit or a native interval type when its calendar semantics fit.

Application and API Compatibility

End-to-end design includes drivers, ORMs, programming languages, JSON, spreadsheets, and downstream analytics. A database BIGINT can exceed JavaScript's exactly representable integer range. DECIMAL may become a string or arbitrary-precision object because binary floating point would lose cents.

Temporal values need explicit offset and time-zone rules. Nullable database columns need nullable application types. Binary data should map to bytes or streams, not accidental text. Test serialization and round trips rather than assuming the database declaration survives every boundary.

Implicit Conversion Risks

When compared types differ, the database may convert one side according to precedence rules. Conversion can fail on dirty data, change results, or prevent an index seek. Product rules differ, so a query that works on one engine can behave differently on another.

Align parameter types with column types and bind values through prepared statements. Avoid string parameters for numeric or temporal columns. Use explicit conversion only at a controlled boundary where failure and precision are understood.

Database Portability

Standard-looking names do not guarantee identical behavior. Integer ranges, VARCHAR length semantics, Boolean support, ENUM syntax, JSON operators, timestamp time zones, decimal limits, UUID support, and large-object APIs vary.

Portable systems can use a conservative common subset and isolate database-specific features. Single-platform systems can use mature native types where their correctness and performance benefits exceed migration concerns. Document every vendor-dependent assumption and test schema generation on all supported releases.

Plan for Growth without Guessing

Capacity planning should include creation rate, retention period, tenant growth, geographic expansion, and future integrations. Current sample data is rarely enough. An ID range that lasts decades at today's rate may exhaust quickly after a bulk import or event-stream rollout.

Avoid speculative over-engineering. Unlimited text, maximal decimals, and BIGINT everywhere can weaken validation and increase costs. Select a defensible range with measured headroom and define thresholds that trigger review before exhaustion.

Schema Evolution Costs

Type changes can rewrite a large table, lock metadata, rebuild indexes, expand transaction logs, affect replicas, and require application coordination. Widening is usually safer than narrowing, but every product and conversion path differs.

Changing DECIMAL scale can round or reject values. Converting VARCHAR to numeric exposes invalid strings. Changing time-zone semantics can shift historical instants. Treat type migration as a data project, not a one-line DDL operation.

Safe Type Migration Workflow

  1. Profile existing values, NULLs, ranges, formats, and outliers.
  2. Define conversion, rounding, invalid-data, and rollback rules.
  3. Inventory indexes, constraints, views, procedures, reports, and clients.
  4. Add the new representation and dual-read or dual-write when needed.
  5. Backfill in bounded, observable batches.
  6. Compare counts, checksums, aggregates, and sampled records.
  7. Switch readers and writers in a controlled release.
  8. Remove the old representation only after recovery requirements are met.

Data Profiling before Selection

When designing from existing data, inspect minimum, maximum, distinct count, NULL rate, longest value, invalid formats, leading zeros, precision, and distribution. Profile by source because one integration may use different conventions from another.

Sample data can hide rare but valid values. Confirm contractual limits and future requirements with domain owners. A type chosen only from one month of records may fail during year-end processing or international expansion.

Document the Domain

A data dictionary should record column meaning, logical type, physical type, unit, allowed range, precision, null meaning, default, constraints, examples, source, owner, sensitivity, and retention. Documentation explains decisions that DDL alone cannot express.

Keep schema migrations and documentation synchronized. Review type decisions with application developers, QA, analysts, and operations because each layer exposes different risks.

Percentages, Rates, and Ratios

A percentage needs both a numeric type and an agreed representation. The same ten percent might be stored as 10.00 or as the ratio 0.1000. Either can work, but mixing conventions creates calculation defects that no SQL type can detect by itself. Name the column clearly, document the unit, select enough scale for the business calculation, and add a CHECK constraint for the permitted range.

Some rates legitimately exceed 100 percent or become negative, while discount percentages may be limited to zero through 100. Do not copy one constraint to every rate column. Tax, interest, growth, probability, and completion values have different rules. Preserve extra calculation precision and round only at a defined business boundary.

Geospatial and Network Values

Latitude and longitude can be held in DECIMAL or DOUBLE when the system performs simple storage and display, but a spatial type is stronger when applications need containment, distance, coordinate systems, or spatial indexes. Coordinate precision should reflect measurement accuracy; storing many digits does not make an imprecise reading more accurate.

IP addresses should not be forced into one 32-bit integer because IPv6 requires a wider representation and textual forms can have several equivalent spellings. A native network type provides validation and network operators where available. Otherwise, use a deliberately designed binary or canonical text representation that supports both address families.

Security-Sensitive Values

Passwords should never be stored as plaintext or reversibly encrypted text. Store the output of an approved password-hashing scheme in a representation sized for its encoded format, and retain algorithm parameters as required. Cryptographic hashes, salts, nonces, encrypted payloads, and signatures are byte sequences unless an external interface specifically requires Base64 or hexadecimal text.

API keys and tokens need length, comparison, collation, and indexing decisions based on their threat model. Often the database stores a hash rather than the recoverable token. Avoid case-insensitive collations for opaque secrets. Mark sensitive columns in the data dictionary and consider masking, logging, backup, and access-control behavior alongside the physical type.

Generated, Derived, and Historical Values

Ask whether a value should be stored at all. Age is derived from birth date and changes over time; storing it creates inconsistency. An order total may be derived, yet retaining the finalized amount can be correct because it is a historical financial fact that must not change when product prices change. The domain determines whether derivation or snapshot storage is appropriate.

Generated columns can expose a typed expression while keeping one source of truth, but database support and indexability vary. If a derived value is persisted, define when it is recalculated and how discrepancies are detected. Its type must handle the result of the full expression, not merely the type of one input.

Operational Review of Type Decisions

A production review should examine more than the CREATE TABLE statement. Estimate row width, index width, expected cardinality, write volume, retention, partition keys, replication traffic, backup size, and archive format. Review frequently filtered columns and confirm their types match bound parameters. Check whether large values are loaded eagerly by the ORM or copied into events and logs.

Monitor actual maximum IDs, text lengths, decimal ranges, invalid-input rejection, and NULL rates after deployment. Trends reveal when assumptions are changing. Alerts should provide enough lead time to widen a type through a rehearsed migration rather than during an outage. Observability turns a one-time design estimate into a maintained capacity decision.

A Repeatable Team Decision Process

For important columns, write a short decision record: business definition, candidate types, selected type, rejected alternatives, constraints, application mapping, and migration assumptions. This makes tradeoffs reviewable and prevents later teams from replacing an intentional representation because it looks unusual.

Start with domain experts, then validate with database, application, analytics, security, and QA perspectives. Use representative production-like values and execute the dominant queries. The result does not need to be the smallest possible declaration; it needs to be correct, explainable, testable, efficient for the workload, and practical to evolve.

Practical Selection Examples

DataRecommended starting pointReason
Phone numberVARCHARIdentifier with formatting, not arithmetic
Postal codeVARCHARLeading zeros and regional formats
MoneyDECIMAL plus currency codeExact arithmetic and explicit unit
Birth dateDATENo invented time or zone
Created eventTimestamp with defined instant policyTime and precision matter
SHA-256 hashBINARY(32) or documented hex textFixed representation
UUIDNative UUID or BINARY(16)Validation and compact semantics
Flexible product attributesJSON with relational coreVariable secondary structure
Dynamic categoryLookup table and foreign keyManaged entity with metadata
Scientific measurementDOUBLE or domain-specific DECIMALChoose approximate or exact deliberately

Example Customer Table

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_code VARCHAR(30) NOT NULL UNIQUE,
    full_name VARCHAR(200) NOT NULL,
    email_address VARCHAR(320),
    birth_date DATE,
    created_at TIMESTAMP NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

The generated numeric key supports relationships, while the stable customer code remains unique. Name and email are variable text, birth date has no invented time, creation is an event, and active state is a required Boolean. Additional constraints and case-handling rules depend on the product contract.

Example Order Table

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    currency_code CHAR(3) NOT NULL,
    subtotal DECIMAL(14, 2) NOT NULL CHECK (subtotal >= 0),
    tax_amount DECIMAL(14, 2) NOT NULL CHECK (tax_amount >= 0),
    status VARCHAR(20) NOT NULL
      CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
    ordered_at TIMESTAMP NOT NULL,
    shipped_at TIMESTAMP NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

The schema separates amount from currency, constrains nonnegative values, uses controlled status codes, and represents an event that has not occurred with a nullable timestamp.

Common Data Type Selection Mistakes

  • Using VARCHAR for every column to avoid initial analysis.
  • Storing phone numbers and postal codes as numeric quantities.
  • Using FLOAT for exact money or VARCHAR for numeric calculations.
  • Choosing CHAR for naturally variable text.
  • Using maximum-length or unbounded text everywhere.
  • Storing dates and timestamps as formatted strings.
  • Using a timestamp for a calendar-only birth date.
  • Using Boolean for a multi-state workflow or ENUM for dynamic entities.
  • Hiding core keys, prices, and statuses inside JSON.
  • Using BLOB for structured data that needs queries and constraints.
  • Ignoring NULL meaning, units, application precision, or collation.
  • Choosing from current samples without growth and migration analysis.

Data Type Selection Checklist

  1. Define the business meaning and legitimate operations.
  2. Classify the value as quantity, text, temporal, logical, binary, or document.
  3. Determine range, length, precision, scale, unit, and growth.
  4. Decide exact versus approximate numeric behavior.
  5. Define nullability, default, and missing-data semantics.
  6. Add constraints that narrow the physical type to the domain.
  7. Review joins, filters, sorting, aggregation, and index needs.
  8. Verify driver, language, ORM, API, and analytics mappings.
  9. Check product-specific behavior and portability requirements.
  10. Estimate migration, backup, replication, and future-widening costs.

Testing Type Decisions

Test minimum, maximum, typical, boundary, over-range, NULL, and invalid values. Include Unicode, leading zeros, fractional precision, rounding, time-zone boundaries, daylight-saving transitions, empty binary values, and large documents where relevant.

Round-trip values through the driver, ORM, API serializer, event stream, and report export. Verify that BIGINT, DECIMAL, timestamps, UUIDs, and NULL survive without truncation or reinterpretation. Inspect query plans for implicit conversions and realistic index behavior.

Migration tests should include dirty legacy values, locking, retries, rollback, replica lag, and verification queries. A technically valid conversion is incomplete until business totals and representative records match.

Interview Questions and Answers

Why is data type selection important? It controls validity, precision, operations, storage, indexing, application mapping, and migration cost.

Should a phone number use BIGINT? Usually no; it is an identifier with formatting and international conventions, so character storage is more appropriate.

Why should money use DECIMAL instead of FLOAT? DECIMAL provides exact base-10 arithmetic, while FLOAT is approximate.

When should CHAR be used? Use it for values that are truly fixed length under the chosen character semantics.

Why not store dates in VARCHAR? VARCHAR permits invalid values and weakens arithmetic, range filtering, sorting, and indexing.

When is JSON appropriate? It fits flexible secondary attributes, not stable relational keys and core business fields.

Why not use maximum type sizes everywhere? Oversized types can weaken constraints, increase indexes and memory, and complicate tooling without improving correctness.

What is the first selection question? Ask what the value means and which operations are valid, not what its sample text looks like.

Key Takeaway

The correct SQL type is the smallest truthful representation that preserves business meaning, required range and precision, valid operations, and end-to-end compatibility. Performance tuning follows correctness; storage width should never override semantic accuracy.

GOOD TYPE SELECTION = BUSINESS MEANING
                    + RANGE AND PRECISION
                    + NULL AND CONSTRAINT RULES
                    + QUERY AND INDEX NEEDS
                    + APPLICATION COMPATIBILITY
                    + MANAGEABLE EVOLUTION

Model quantities as numbers, identifiers as identifiers, dates as temporal values, bytes as binary, and flexible documents as JSON only when flexibility is real. Add constraints, document units and assumptions, test every system boundary, and plan growth without replacing analysis with oversized generic types.