I am doing an ODBC based SQL client and I need to store a boolean value - what is the most portable SQL data type to use for the corresponding column? I already saw the related questions What is the best data type to store boolean values in a database? and Are there reasons for not storing boolean values in SQL as bit data types?, but they do not cover portability per dbms. I assume TINYINT is the way to go since it seems that at least MySQL and MSSQL support it, but maybe someone can give a more precise answer from experience?
What is a portable SQL data type for storing boolean values?
814 Views Asked by Rudolfs Bundulis At
1
In an ideal world, there would be a standardized boolean data type in all platforms, but that's not reality. Realistically, SMALLINT is probably the most portable data type for cross-platform use in storing boolean values. With proper constraints and defaults it makes a decent substitute.
There's significant historical precedent for using integers to represent boolean values where true booleans are not available. This makes sense because booleans are closer to numeric values, with arithmetic operations, etc, than they are to other SQL data types.
SMALLINT is relatively small (typically 2 bytes on most platforms), so it doesn't cause unnecessary bloat; it's a numeric data type, which means it can be evaluated as numeric values 1 and 0 without casting or other logic; and it's a supported data type under the ANSI-92 standard, which means it's well-supported by many different database platforms.
TINYINT would be better from the size perspective, but isn't a standard data type, and is less widely supported than SMALLINT. Using a CHAR would also be smaller than SMALLINT, but at the cost of not being numeric, thus requiring additional logic to convert it to numeric form.