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?
827 views Asked by Rudolfs Bundulis At
1
There are 1 answers
Related Questions in SQL
- SQL schema for a fill-in-the-blank exercise
- Hibernate: JOIN inheritance question - why the need for two left joins
- What's supposed to be the problem in this query?
- Compare fields in two tables
- How to change woocomerce or full wordpress currency with value from USD to AUD
- Dynamic query creation with Array like implementation
- SQL query to get student enrolled in this month in a course - Moodle
- SQL LAG() function returning 0 for every row despite available previous rows
- Convert C# DateTime.Ticks to Bigquery DateTime Format
- Use row values from another table to select them as columns and establish relations between them (pivot table)
- SQL: Generate combination table based on source and destination column from same table
- how to use system's environnement variables in sql script
- PHP fetchAll on JOIN
- Multitable joining in Sql
- How to display name starting from 'z' by using BETWEEN cmd only?
Related Questions in BOOLEAN
- How to pass bool value from one class to another class in flutter
- How to change variable inside a method
- Is it possible to prevent implicit bool <-> int conversions?
- Why is $scope >= 0 showing true in interpolation while empty in controller?
- Is there a shorthand way to say if a bool is already true don't assign false?
- return a bool value from a Future<bool> function in flutter
- java List with all combinations of 8 booleans
- Ternary Operator not displaying row when condition is false but displays when true
- I am new to Boolean Algebra, and i need advice
- How is this (xy)' + (yz) simplified into this (x’+y’)+z in boolean algebra?
- The Beginner's Guide to Boolean Search Operators
- Octave: Boolean AND returns wrong number
- Azure Dev Ops: How can I require a boolean field to be true before state can be changed?
- truth value of empty user-defined data objects
- In python, 13 and 9 = what?
Related Questions in SQL-TYPES
- Postgres plsql return one column from table with dynamic type
- ORACLE : How to add a nested table to an existing type
- PLSQL aggregation function using type object PARALLEL_ENABLE AGGREGATE
- Postgresql Jsonb Java Hibernate 6 JdbcTypeCode
- Getting error when retrieving Spatial Data from my database
- MySQL alternative to PostgreSQL's custom data type (and domain)
- Avoid error out of range SQL during database migration
- Converting SQL type to PLSQL collection / converting one collection type to another
- using SqlGeography types in C# client-side
- SQL Server compatibility creates issue
- Assign ref variable value oracle
- Is There a Way to Convert System.Data.SqlTypes.SqlBinary of Datatable To Type System.Byte[]?
- DataTable not populating a datatype fully in sql server
- create a PySpark Schema for a tuple composed of a list and an array
- Insert data into user-defined type elements of a table in postgresql
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
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.