Schema & Design
SQL Data Types
SQL data types define the kind of value a column, variable, parameter, or expression can hold.
Overview
SQL data types define the kind of value a column, variable, parameter, or expression can hold. They constrain storage, determine which operators and functions are valid, and influence how the database engine stores and compares values. The SQL standard introduced data types in SQL-86 and has expanded them in later revisions, adding types such as exact and approximate numerics, character strings, bit strings, datetime, interval, boolean, and large objects. Each type has a domain of legal values and a set of permitted operations. For example, numeric types support arithmetic, while character types support concatenation and pattern matching. Choosing an appropriate type affects data integrity, query semantics, and index usability. A column declared as INTEGER rejects non-numeric input, whereas a column declared as VARCHAR(50) accepts text up to fifty characters. Because SQL is vendor-neutral in principle but implemented differently in practice, most database products provide the standard types plus proprietary extensions. The type system is therefore both a logical contract for application developers and a physical hint for the storage engine. Understanding data types is foundational to schema design, because every table definition, constraint, and index ultimately rests on the types assigned to its columns.
Syntax and usage
Data types appear in several SQL statements. In CREATE TABLE, each column is followed by a type: CREATE TABLE t (id INTEGER, name VARCHAR(50), price DECIMAL(10,2)). In ALTER TABLE, types can be added or changed: ALTER TABLE t ADD COLUMN created_at TIMESTAMP. In CAST, a value is converted explicitly: CAST(price AS INTEGER). In CREATE DOMAIN, a user-defined type can be named and reused. Standard SQL groups types into categories: character strings (CHAR, VARCHAR, CLOB), exact numerics (NUMERIC, DECIMAL, INTEGER, SMALLINT, BIGINT), approximate numerics (FLOAT, REAL, DOUBLE PRECISION), bit strings (BIT, BIT VARYING), datetime (DATE, TIME, TIMESTAMP), interval (INTERVAL), boolean (BOOLEAN), and binary large objects (BLOB). Many engines add types such as TEXT, SERIAL, UUID, JSON, and ARRAY. Parameterized types specify precision, scale, or length, for example NUMERIC(12,4) or VARCHAR(255). Usage rules vary: some types are implicitly convertible, others require explicit CAST. Comparisons between different types follow coercion rules defined by the standard and refined by each vendor. When a type is omitted in a column definition, behavior is implementation-defined, so explicit typing is strongly recommended.
How it works
At the logical level, a data type defines a value space and a set of operations. The database enforces the type when rows are inserted or updated, rejecting values outside the domain or coercing them according to defined rules. At the physical level, the type determines the storage representation: fixed-length types such as CHAR(10) and INTEGER typically occupy a predictable number of bytes, while variable-length types such as VARCHAR and BLOB store a length indicator plus the actual bytes. Numeric types differ in range and precision. Exact types such as DECIMAL store decimal digits without binary rounding, while approximate types such as FLOAT store binary floating-point values and may introduce rounding error. Character types are associated with a collation that governs comparison and sorting. Datetime types store date, time, or both, often with fractional seconds and time zone information. Type conversion occurs implicitly in many expressions, but implicit coercion can change results or prevent index use. Indexes are built over typed values, so the type of a column affects index ordering and the applicability of operators. Constraints such as CHECK, NOT NULL, and foreign keys operate on typed values, which is why type consistency across related columns matters for both correctness and performance.
Examples
A table using several standard types: CREATE TABLE product (id INTEGER PRIMARY KEY, sku CHAR(8) NOT NULL, name VARCHAR(100), price DECIMAL(10,2), weight REAL, launched DATE, active BOOLEAN DEFAULT TRUE). Inserting a row: INSERT INTO product (id, sku, name, price, weight, launched, active) VALUES (1, 'A1000001', 'Widget', 19.99, 0.75, DATE '2024-01-15', TRUE). Explicit conversion: SELECT CAST(price AS INTEGER) FROM product. Using a parameterized numeric type: CREATE TABLE measurement (id INTEGER, reading NUMERIC(8,3)). Comparing a character value with a numeric literal may trigger coercion: SELECT * FROM product WHERE sku = 1000001. In standard SQL, DATE '2024-01-15' is a typed literal, while '2024-01-15' is a character string that may be implicitly converted. Vendor extensions include SERIAL or IDENTITY for auto-incrementing integers, TEXT for unbounded character data, UUID for universally unique identifiers, and JSON or JSONB for semi-structured documents. A cast that fails, such as CAST('abc' AS INTEGER), raises an error in most engines rather than returning a value. These examples show how type choices appear in everyday DDL and DML.
Common pitfalls
A frequent mistake is choosing an approximate numeric type such as FLOAT or REAL for monetary values, which can produce rounding artifacts; exact types like DECIMAL or NUMERIC are usually preferred for money. Another pitfall is relying on implicit conversion between character and numeric types, which may silently change comparison semantics or prevent an index from being used. Declaring VARCHAR without a length is invalid in standard SQL, though some vendors allow it with implementation-defined behavior. Using CHAR for variable-length data wastes space and can introduce trailing-space comparison quirks. Storing dates as strings loses validation and range operations. Mixing types across joined columns, such as an integer key and a character key, forces coercion and can degrade performance. Nullability is separate from type: a column of any type may allow nulls unless NOT NULL is specified, and null comparisons follow three-valued logic. Finally, type changes through ALTER TABLE may rewrite data or fail if existing values cannot be converted, so migrations should be tested. Understanding these traps helps avoid subtle bugs in queries, constraints, and indexes.
Best practices
Choose the smallest type that safely holds the expected range of values, but avoid premature micro-optimization that sacrifices clarity. Use exact numeric types for currency and other values requiring precise decimal arithmetic. Prefer standard type names over proprietary synonyms when portability matters, and document any vendor-specific types in the schema. Always specify length, precision, and scale explicitly rather than relying on defaults. Use NOT NULL where the domain excludes nulls, and add CHECK constraints to enforce value ranges that the type alone cannot express. Keep related columns type-consistent, especially primary and foreign keys, so joins and indexes remain efficient. Avoid storing structured or datetime data in character columns. When converting types, use explicit CAST so intent is clear and errors surface early. Review type choices during schema design and again before large migrations, because changing a column type can affect indexes, constraints, and application code. Consistent, deliberate typing improves data integrity, query predictability, and long-term maintainability.
Frequently asked questions
What is the difference between CHAR and VARCHAR? CHAR(n) is fixed-length and pads values with spaces to length n, while VARCHAR(n) is variable-length and stores only the characters supplied, up to n. CHAR can be slightly faster for truly fixed-width codes, but VARCHAR is generally more space-efficient for variable text. Why does SELECT 0.1 + 0.2 sometimes not equal 0.3? If the values are approximate numeric types such as FLOAT or REAL, they are stored in binary floating point and cannot represent some decimal fractions exactly, so the result may differ slightly. Use exact types such as DECIMAL when exact decimal behavior is required. Can a column have no data type? In standard SQL, every column definition requires a type; some vendors permit omissions with implementation-defined defaults, but explicit typing is recommended for portability and clarity.