none
using bit datatype as bool in TSQL

    Question

  • I've been doing SQL Server for a long time now and only recently saw code using bit as boolean with constants 'True' and 'False' converting to 1 and 0 respectively.  They do convert properly if the datatype is bit.  They do NOT convert automatically if the datatype is int!

    How long has this been going on?

    Is it a best practice or a horribly kludge to use this?

    Thanks,

    Josh

    ps - and for extra credit why on Earth does SQL Server not include a real boolean datatype after all these years?

    Wednesday, June 26, 2013 10:39 PM

All replies

  • I know you have been able to use 'True' and 'False' at least since SQL 2005.  I think you have been able to do it a lot longer than that, but I no longer keep SQL 2000 and older BOL's and my memory has been known to be faulty.

    And, yes you can use them to store 0 or 1 in a bit variable or column, but not in other numeric datatypes.  That sort of problem is not unique to bit datatypes.  It happens with other numeric datatypes.  For example, you can put '2.3E5' into a float, but not a decimal.  And you can put '$23.45' into a money, but not a float or a decimal.  That's why the new SQL 2012 function TRY_CONVERT() is so useful.  It lets you easily check whether the conversion of a value to a given datatype is legal.

    For best practice, my recommendation would be for your site to pick a method and always use it.

    Why questions are difficult.  Boolean were not part of the ANSI SQL standards originally (I don't know whether they are now or not).  So most implementations of SQL did not have them.  Some implementations have added them and some have not.  I guess the reason is probably choices the Microsoft development group makes.  If they implemented them, then that would mean something else didn't get done.  (Although, to be complete, there are SQL purists out there who are passionately against boolean datatypes.  If Joe Celko sees this thread you are likely to get a message telling you that booleans are assembly language flags and have no place in SQL - of course, he has the same opinion about using the bit datatype.)

    Tom

    Thursday, June 27, 2013 5:30 AM
  • >> Is it a best practice or a horribly kludge to use this? <<

    BIT in T-SQL has a weird history. At one point it was a real bit: {0, 1} classic computer data type with classic Sybase/UNIX era operators from C, the language in which T-SQL was written. But then it became a NUMERIC data type {0, 1, NULL} and subject to numeric rules. This lead to some really awful problems. People recompiled code under the assumption that it was still okay to leave off NOT NULL in the DDL. Try and find that one when your code blows up on you! 

    We do not use flags in RDBMS. In fact, the Boolean that was in ANSI/ISO Standards for awhile is be deprecated now. We realized how much of a nightmare a BOOLEAN data type made of SQL's 3-valued Logic. The details involve a lot of math, but Bruce was our logician, having trained under Raymond Smullyan 

    In 25 words or less, SQL is a predicate language and not a flag language. Assembly and lower level systems languages are for bits. A flag is set to leave a signal from one independent process to a second independent process that follows in a temporal sequence. There is no concept of a schema that models the whole universe of discourse. In SQL, things are happening in a concurrent environment. Predicates tell us the current state of the schema. 

    In ANSI/ISO Standard SQL we have a predicate for testing a predicate:
     <search condition> IS [NOT] [TRUE | FALSE | UNKNOWN]
    This works and existed internally in the SQL compilers for DDL and DML where the rules are different. 

    I have a detailed article on this topic (I am not going to cut & paste a few thousand words and code)

    https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/ 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, June 27, 2013 5:19 PM
  • Joe, that's silly.

    As regards booleans, all your noise comes down to two things, first that null values confuse some people, which is no doubt true but is not really an argument against a boolean datatype any more than it is against any other datatype, and second that no interesting real-world objects or conditions have just two interesting values, which is clearly false.

    I can make no sense at all out of your talk about "flags".  Nobody says flags have just two values, and I don't see what's wrong with representing flags in a data model.

    It's a vaguely interesting bit of history that bit was a non-number before it was a number, but I'm not even talking about bit, I'm talking about bool.  Well OK I'm asking about the current oddity, for what it's worth, of using a bit with quoted values as semi-bools.

    But any language that has an if statement already has boolean expressions, any language with a bit datatype is nearly there with bool, and how hard is it really to close the loop here? 

    In regards to other bit datatypes we have all kinds of BLOBS and images and binaries, so what *are* you ranting about?

    Josh

    ps - anyone else have a hard time logging into MSDN the last twenty-four hours?  I've had blank login pages on several workstations and several browsers.  Suddenly this one is working.  I think.

    Friday, June 28, 2013 1:59 AM
  • >> As regards Boolean, all your noise comes down to two things, first that NULL values confuse some people, which is no doubt true but is not really an argument against a Boolean data type any more than it is against any other data type, .. <<

    The people it confuses are logicians and mathematicians. Dave McGovern did some nice papers papers showing that the 3VL in SQL is a not a predicate logic; it lack inference rules. When you read the ANSI/ISO Standards, did you wonder why we went out of our way to says “search condition” and not “predicate”? That was Bruce holding our feet to the fire. 

    We tried putting BOOLEAN into SQL and it does not work! That is why it is coming out now. 

    >> .. and second that no interesting real-world objects or conditions have just two interesting values, which is clearly false. <<

    But that is not the same thing! Try rH blood factor (plus or negative) as an example. There is a well-defined, limited nominal scale for it, there is a physical test for it and NULL means “untested on that scale”; this is not the same thing as a general, high level abstract value, like TRUE, FALSE,and UNKNOWN which apply to every possible valid predicate in the universe and not entities. 

    >> I can make no sense at all out of your talk about "flags". Nobody says flags have just two values, and I don't see what's wrong with representing flags in a data model. <<

    Dijkstra did :) His classic papers on Semaphores? Every Assembly language? 

    >> It's a vaguely interesting bit of history that bit was a non-number before it was a number, but I'm not even talking about bit, I'm talking about bool. Well OK I'm asking about the current oddity, for what it's worth, of using a bit with quoted values as semi-bools.<<

    Semi-Bool?? But BIT (uppercase, reserved word) has been a T-SQL problem and it is a little more than history. It is a pain! 

    >> But any language that has an IF statement already has Boolean expressions, any language with a bit data type is nearly there with bool, and how hard is it really to close the loop here? <<

    SQL is declarative, so it does not even have statements! There is no control flow. And it is mathematical impossibility in the SQL model because of 3VL and the desire to keep the propagation of NULLs with type casting. 

    >> In regards to other bit data types we have all kinds of BLOBS and images and binaries, so what *are* you ranting about? <<

    Everything is bits in a computer. That is the representation and not the semantics. BOOLEAN is a semantic concept at a higher level. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 28, 2013 3:36 AM
  • I'll just say again, if you want to argue for removing NULL from SQL - that is, from the relational model - then you may have an argument.  

    But if it's going to have datatypes at all, I see no reason why bool is any more problematic than int.

    And if the people who are confused about it are logicians, then maybe they ought to spend some time programming and they just might be surprised at what they learn.

    Josh


    • Edited by JRStern Friday, June 28, 2013 5:15 AM and if ...
    Friday, June 28, 2013 5:13 AM