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?
826 Views Asked by Rudolfs Bundulis At
1
There are 1 best solutions below
Related Questions in SQL
- Can MVC.NET prevent SQL-injection at razor or controller level?
- SQL server not returning all rows
- When dealing with databases, does adding a different table when we can use a simple hash a good thing?
- Creating a parametrized field name for a SELECT clause
- Combine two rows based on common ID
- Column displays each count
- Slick query for one to optional one (zero or one) relationship
- Aggregate and count in PostgreSQL
- MAX and GROUP BY - SQL
- SQL statement for a tricky 2 table query
- How to create nested selects with sql?
- Pull and push data from and into sql databases using Excel VBA without pasting the data in Excel sheets
- Best Practice for adding columns to a Table in Oracle database
- SQL FIFO STACK using two tables
- SQL Query - Order by String (which contains number and chars)
Related Questions in BOOLEAN
- Trying to set hidden input's value to toggle between True and False in JavaScript
- Shiny: Reactive boolean variable across functions in server.R
- How to change Boolean array to double array
- How to count 2 different duplicate values in array - Swift
- How to perform boolean addition on an SQL select
- Validation within Get/Set Methods Java
- Scala: "explicit return" required?
- Rails object cannot list values where Boolean is true
- Checking strings
- PHP return type not as same as i expect
- Why "if" is not doing anything in this boolean method?
- Change image.Source in ListView according to boolean
- Compare value and return a bool in Swift
- not (not false) = True?
- MySQL error store boolean value into tinyint
Related Questions in SQL-TYPES
- What is a portable SQL data type for storing boolean values?
- Primary Key as domain username AND userID?
- Is it possible to define bit column in sqlite?
- Sql Spatial Type, Code First migration and Azure DB
- Varchar Encoding in MySQL
- MySQL alter table - changing a TINYINT to a SMALLINT
- Set decimal(16, 3) for a column in Code First Approach in EF4.3
- Automapper value type to SqlType conversions
- Invalid data type while using user defined table type
- Decimal(19,4) or Decimal(19.2) - which should I use?
- MySQL alternative to PostgreSQL's custom data type (and domain)
- What are SQL Server data-types mapped to by default in ADO.NET?
- datetime vs smalldatetime
- CREATE FUNCTION for <> T-SQL and CLR types for return value do not match
- using SqlGeography types in C# client-side
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)
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?
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.