Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Question Unanswered: Does bit data type function as boolean?

    Hi,

    I have some applications working fine with Sql Server 7 and Jet 4.
    I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:

    These query worked fine in Sql Server 7:

    "Select <fields> from <table> where <boolean/bit field>"

    Now I have to do this way:

    "Select <fields> from <table> where <bit field> = 1"

    Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.

    Does anyone know why this is happening? Does Sql Server work different than older versions?

    Thanks!

    Fabiano

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Does bit data type function as boolean?

    http://dbforums.com/arch/43/2002/4/343538



    Originally posted by fabiano
    Hi,

    I have some applications working fine with Sql Server 7 and Jet 4.
    I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:

    These query worked fine in Sql Server 7:

    "Select <fields> from <table> where <boolean/bit field>"

    Now I have to do this way:

    "Select <fields> from <table> where <bit field> = 1"

    Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.

    Does anyone know why this is happening? Does Sql Server work different than older versions?

    Thanks!

    Fabiano

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice thread, sbaru

    especially joe celko's advice not to use BIT

    did you notice that no one actually answered the question "...so the correct, non-proprietary datatype for representing a boolean value is...?"

    fabiano, your question "Sql Server 2000 doesn't recognize a boolean value anymore?" is not quite accurate -- i don't think it ever did

    if you can change the datatype, you might consider a numeric field like TINYINT or INTEGER instead of BIT

    see also Booleans in SQL Server


    rudy

  4. #4
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Thumbs up Just one more question.

    Thanks Rudy and Sbaru,

    I've been advised to use tinyint (or some integer variant) to do it. I have some doubts about use tinyint instead of bit to represent a boolean type as follows:

    1. Didn't bit type created to represent boolean values? Why does it exist? It what kind of situation am I going to use it?

    2. Is bit an optimized type to work with?

    3. I know it doesn't make sense to summarize or group by a bit field but it's very common to use this type of field to count records in a table or query by it.

    Ex:
    Employees that have dependents:

    "Select Cod_emp from Employees Where Dep_emp"

    Count the number of these employees:

    "Select Count(Cod_emp) from Employees Where Dep_emp"

    These queries above are common in some situations.

    Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read.

    I have many queries this way. If I change the bit types to tinyint will they work fine or I will have to change the structure of all my queries?

    Thanks for your time!

    Fabiano

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1) no, BIT was created to store bits

    2) depends what you mean by "optimized"

    3) "Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read."

    whether it is clearer to read is a matter of opinion

    "Dep_emp" by itself means nothing to me

    "Dep_emp = 1" means nothing to me either

    if the purpose of the Dep_emp column is to indicate whether the employee belongs to the department, then i suggest that it is not at all clear, and in fact is poor design

    but yes, you are going to have to change the structure of all your queries


    rudy

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It doesn't work that way..

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (col1 bit)
    GO
    
    INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 0
    GO
    
    --Doesn't work
    SELECT * FROM myTable99 WHERE Col1 TRUE 
    --Works
    SELECT * FROM myTable99 WHERE Col1 = 1
    GO
    
    DROP TABLE myTable99
    GO
    And what is dep_emp...it must be predefined somewhere..

    Also it sounds like all of your SQL code is in the app...now would be a good time to turn them in to stored procedures
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Thumbs up Understood

    I understood. I'm going to change some queries in my applications.

    I've already have most of my queries in stored procedures and views, and these queries use boolean fields in criteria search the way I've described.

    Returning to those doubts I've mentioned:

    1) Yes. Bit fields were created to store bits. But bit value seems to use in boolean cases. When do I need to store pure bits? If the type was byte instead of bit it would make sense, but bit? Bit value accepts only 0 or 1, True or False, Yes or No or some variance of it. That's why I've thought in use it to store this type of value. But it's ok! I've understood that it won't work this way.

    2) When I've mentioned "optimized" I wanted to know if Sql Server treat this type of field in a different way. Do you know if it proceeds?

    3) The field "Dep_emp" was not the better example. Think in a gender field to store "male" or "female" where "true" could be "female" and so on. In this case a query like "Select Name_cus from Customers Where Gen_cus" would return just the females ones in a case of a promotion for the female customers for example. In this case, untill now, I didn't have to explicit "Where Gen_cus = 1". I've been used just "Where Gen_cus" that it seems to be clearer once you think in "boolean way".

    My doubt is why did MS change the way of treatment of bit fields in Sql Server 2000? Any other DBMS or older versions of Sql Server recognizes the ANSI-SQL "Where <boolean field>" without the need of explicit the true or false value. Once it's a boolean field it doesn't make sense to explicit the value, right? It's true or false, nothing more. I thought this type of field was created to treat these situations in a "optimized" way, even just to clear the queries.

    This way of treatment of boolean values helps us even more than this. In a table that stores the quantity of dependents of a employee (like children, parents, ...) you will be able to query for employees that have dependents just using the boolean way, for example:

    "Select Cod_emp from Employess Where Dependents_emp"

    Or

    "Select Cod_emp from Employees Where Not Dependents_emp"

    Note that I don't need to explicit "Where Dependents_emp > 0" or "Where Dependents_emp = 0". I don't need, in this case, to know how many dependents each employee has, just to know who they are. There're many reasons for this kind of query. This way of query worked not just in bit fields but in all numeric fields. Any value different than 0 was treated as true.

    The biggest problem is that I'll need to separate my apps in 2 versions. One for any DBMS and one just for Sql Server 2000. I'm not worried about the performance using a "native" Sql of each DBMS. I'm worried about the portability of my apps to any DBMS, that's why I often use ANSI-SQL, even because the performance boost is not too sensitive.

    Thanks again!

    Fabiano

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i could be wrong, but i don't think sql server has ever supported boolean

    it goes against everything we've come to expect from microsoft, that they would have a standard feature in an early database and then stop supporting it -- that just doesn't make sense

    as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean...

    "Any value different than 0 was treated as true" ???

    that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know, and all i can say is, i hope i never have to support one of your apps, because that is certainly counter-intuitive

    at least, to me

  9. #9
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    I guess my english is making a confusion here

    Hi Rudy,

    Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.

    It seems to me that you got angry about anything I've said in your words:

    "...whether it is clearer to read is a matter of opinion
    Dep_emp by itself means nothing to me
    Dep_emp = 1 means nothing to me either..."

    That's not possible that you couldn't see the mean of these codes. It's very clear. You don't need to know the total structure of the project to suppose that it's a query that brings rows where "Dep_emp" is "true". Just it!

    -----

    "...and in fact is poor design."

    How can you say something like that without the knowledge enough about the project?

    -----

    "i could be wrong, but i don't think sql server has ever supported boolean..."

    Yes. You're wrong. The bit field was treated as boolean untill Sql Server 7. My apps stopped to work only when I upgraded to Sql Sever 2000.

    ------

    "as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean..."

    The more experience more creativity, indeed.

    ------

    "Any value different than 0 was treated as true ???
    that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know..."

    I think you don't have a basic knowledge about another RDBMS than Sql Server 2000. This behavior has been standard at least in Jet, Sql Server (untill 7), MySql and Postgre SQL. I'm working with all these ones and I can say that they work fine.

    ------

    "...i hope i never have to support one of your apps, because that is certainly counter-intuitive."

    Yes. You're totally right. My apps need more "creativity" and I think you've not got it enough.

    -----

    Please take a good look at the links below (they're all from Microsoft as you can see):

    http://support.microsoft.com/default...b;en-us;129803
    "0=False, non-zero=True"

    http://msdn.microsoft.com/library/de...datBoolean.asp
    "When numeric data types are converted to Boolean values, 0 becomes False and all other values become True."

    http://msdn.microsoft.com/library/de...on_03_7uzy.asp
    "Converting to bit promotes any nonzero value to 1."

    http://msdn.microsoft.com/library/de...ba-bz_2it0.asp
    "Microsoft SQL Server optimizes the storage used for bit columns."

    http://msdn.microsoft.com/library/de...le_13_7s6r.asp
    http://msdn.microsoft.com/library/de...le_13_10ha.asp
    *Sqloledb mapps bit data type to boolean data type according to the table shown.

    http://www.microsoft.com/technet/tre...trans/ch20.asp
    "Efficient datatypes

    ...Narrow columns make narrow tables, and narrow tables allow you to store more rows on a single data page...

    ...If your table contains a series of flags with Yes/No or False/True type of information, the best datatype for such columns is BIT. This type is especially effective when you have several columns of type BIT in a table..."

    http://msdn.microsoft.com/library/de...bspec10_2a.asp
    "...A zero numeric value converts to the literal False. All other numeric values convert to the literal True."

    ----

    You can see that I've followed the MS' suggestions. That's why my disappointment with the change of treatment of the bit type.

    I'm not saying Sql Server is a bad RDBMS. I use it for a long time and I like its performance and stability. I often suggest it for my customers. Then you don't need to get personally offended. It was not intension. Sorry Rudy.

    Fabiano
    IT Solution Engineer

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fabiano, i was not angry, and i am not angry now

    i've been working with databases since the mid '70s, and i do have plenty of experience with them

    what is "creative" to you may not seem so to someone else

    you are entitled to your opinion, and i am entitled to mine, which i have arrived at after working with many different databases for many decades

    i have plenty of creativity, my friend

    good luck to you sir


    rudy

    p.s. you say sql server 7 "treated" BIT as BOOLEAN, but that's not the same thing as supporting BOOLEAN

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Come on Rudy, admit it! Everything you know you learned by scanning the back cover of "Databases For Dummies" at Barne's And Noble!

    It's time to face the music!


  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sheeeeeesh

    flame wars...

    Why are people soooooooo sensitive....

    Is it the ability to lash out in anonymity ?

    And fabiano...chill

    Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.
    That translating part I find goes both ways...with the expression, and the interpretation....

    Take everything with a grain of salt...nobody at the sites (well with the exception of a few) are not here, not to help...

    Now back to the thing....


    Where is your code stored? In an Access front end? VB? Stored procedures?

    And Can you post the DDL and or code so we can have a look?

    Maybe only a Blindman (get the pun) can interpret something we haven't seen.

    I'm very curious as to what you have there (It's the only way we learn too)

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Thumbs up I need to learn a little bit more of english first

    How I was asking for help (I know nobody here is obligated to, it's just a forum) and it seemed Rudy didn't like my insistence in some points. I was testing nobody, just debating to see if I could do something not to need changing my whole code.

    I have some codes in sp, views, using the execute method of ado in vb and some dlls in mts. The job will be enormous considering all projects and tecnologies involved with.

    I think I don't need to post any part of the code. The doubt is simple. Once it doesn't matter if I'm querying from a sp, an execute method or view, Sql Server will be always the "interpreter". If the query was "Select <something> from <some table> Where <some field>", it would be interpreted the same way, independently the method used.

    I know "treat by" and "supporting" are different things, but the mean of it is the same. If I can have a condition search like "Where <bField>" and the RDBMS returns the records means that it treated the content of that field as boolean, even the RDBMS doesn't support boolean types (and that's it what have been done by earlier versions of Sql Server).

    I don't have all of that experience because I began to work with projects design at the mid' 80s (10 years latter). Even so I think the time is not the only manner to measure the ability of a professional. There're plenty of professionals that don't worry about their evolution.

    But it's ok. I've interpreted wrong all of those words I'm sure. Forgive me.

    About the anonymity I think there's something wrong. Fabiano is my true name. Brasil, Goiás, Goiânia is my true physical location. If the problem is a telephone number here it goes: +55 (62) 259-7567 (Commercial. I'll be there from January, 5). I don't speak english very well (as you can see) but if we have patience I guess we can understand each other.

    Fabiano
    Entitled to my opinion: IT Solution Engineer
    Last edited by fabiano; 12-31-03 at 11:29.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fabiano, your english is fine, that was never the problem, in fact, your english is way better than my portuguese



    i understand your frustration, and all i can say is, you have learned a valuable lesson about creativity versus flexibility and maintainability

    i practice what i call "defensive sql" and WHERE BITFIELD by itself is not very defensive, as you have found out

    good luck with your upgrade


    rudy

  15. #15
    Join Date
    Dec 2003
    Location
    Brasil, Goias, Goiania
    Posts
    14

    Thumbs up alright. peace!

    Rudy,

    You don't need to know portuguese once I came for help in a foreign forum (for me ). It's my obligation to know how to express my doubts, I know.

    With the improvement of my english I can see clearly now. It was not your (or Brett's) fault, sorry. It was seemed to me that you've been ironic in your answers, but was not.

    I respect your knowledge. Unfortunatelly the earlier versions of Sql Server did work the way I told, but it's not the problem. That sintax I've demonstrated is ANSI-SQL and not a "native" one.

    I've been using ANSI-SQL thinking the same way of yours "defensive sql". To have not problems at all (unless the performance was critical, although it would be the last thing I was change).

    I think that I'll need to call for mommy...

    Fabiano

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •