NUMERIC vs INTEGER Storage, Performance, and Type Casting in SQLite

Storage Behavior and Type Affinity in NUMERIC vs INTEGER Columns

The core issue revolves around the differences between columns declared with NUMERIC affinity versus INTEGER affinity in SQLite, particularly regarding storage efficiency, arithmetic performance, and unexpected query results due to implicit type conversions. While SQLite’s flexible type system allows for dynamic data storage, these nuances can lead to subtle issues when migrating schemas from other RDBMSes or handling legacy data with mixed-type values (e.g., integers stored alongside empty strings). Below, we dissect the problem into three critical components: how SQLite stores integer values in NUMERIC and INTEGER columns, the performance implications of type affinity in arithmetic operations, and the root cause of unexpected query results requiring explicit casting.

How SQLite Stores Integer Values in NUMERIC and INTEGER Columns

SQLite employs a type affinity system rather than rigid data types. When a column is declared with NUMERIC affinity, it accepts values of any storage class (integer, real, text, blob, or NULL). However, SQLite attempts to convert inserted values to integers or reals where possible. If a value is an integer, it is stored using SQLite’s variable-length integer encoding (VARINT), which consumes 1 to 8 bytes depending on the magnitude of the integer. For example, the integer 3 is stored in a single byte regardless of whether the column has NUMERIC or INTEGER affinity.

The confusion arises from the misconception that NUMERIC affinity inherently consumes more space. In reality, if a NUMERIC column consistently contains integers, its storage footprint is identical to an INTEGER column. The key difference lies in type enforcement: INTEGER affinity columns prioritize integer storage and reject non-integer values (unless they can be converted losslessly), whereas NUMERIC affinity allows a mix of integer and real values. For instance, inserting "3.5" into a NUMERIC column would store it as an 8-byte IEEE-754 float, but inserting "3" would use VARINT encoding.

Why Arithmetic Operations and Comparisons May Require Explicit Casting

The original discussion highlights a scenario where comparisons like WHERE field >= 50 yield unexpected results when field contains empty strings. This occurs because SQLite performs implicit type conversions during query evaluation. An empty string ('') in a NUMERIC column is treated as a text value. When compared to a numeric literal (e.g., 50), SQLite attempts to convert the text to a number. Per SQLite’s type conversion rules, non-numeric text is converted to 0. Consequently, '' >= 50 evaluates to 0 >= 50, which is false, but '' >= 0 would evaluate to true.

This behavior explains why explicit casting (e.g., CAST(field AS INTEGER)) becomes necessary. If the column contains mixed data (integers and empty strings), the implicit conversion of empty strings to 0 can invalidate query logic. For example, a legacy system migrating from Dataflex might store empty strings as placeholders for "unset" values instead of NULLs. In such cases, arithmetic operations on the column would treat empty strings as 0, leading to incorrect aggregations or comparisons unless the values are sanitized (e.g., converting empty strings to NULL) or explicitly cast.

Resolving Schema Design, Data Sanitization, and Performance Optimization

Step 1: Schema Migration to Enforce INTEGER Affinity
Rebuild the table schema to declare columns with INTEGER affinity instead of NUMERIC. This ensures that only integers (or values convertible to integers) are stored, rejecting non-integer values unless explicitly handled. Use ALTER TABLE with caution, as SQLite’s limited ALTER support may require creating a new table and copying data. For example:

CREATE TABLE new_table (id INTEGER PRIMARY KEY, field INTEGER);
INSERT INTO new_table SELECT id, CAST(field AS INTEGER) FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

Step 2: Data Sanitization for Legacy Values
Replace empty strings with NULLs during migration to avoid implicit conversion to 0. This preserves query correctness in comparisons and arithmetic:

UPDATE table SET field = NULL WHERE field = '';

Step 3: Benchmarking Arithmetic Performance
While INTEGER and NUMERIC columns storing integers perform similarly, test critical queries using both affinities. Use SQLite’s EXPLAIN command to compare bytecode execution plans. For large datasets, ensure that indexes on INTEGER columns are leveraged optimally, as type mismatches can prevent index usage.

Step 4: Handling Mixed-Type Data in Queries
For columns that cannot be sanitized (e.g., legacy systems still writing empty strings), wrap the column in COALESCE or conditional expressions to handle non-integer values:

SELECT SUM(COALESCE(field, 0)) FROM table; -- Treats NULLs as 0
SELECT SUM(CASE WHEN typeof(field) = 'integer' THEN field ELSE 0 END) FROM table;

Step 5: Monitoring Storage Efficiency
Use the sqlite3_analyzer tool to inspect the database file’s storage layout. Verify that VARINT encoding is applied to integer values in both NUMERIC and INTEGER columns. If NUMERIC columns inadvertently store real numbers, consider stricter schema constraints or triggers to enforce integer-only storage.

By addressing schema design, data cleanliness, and query patterns, developers can eliminate the need for frequent casting, ensure predictable query results, and maintain optimal storage efficiency in SQLite databases.