Results 1 to 13 of 13

Thread: Use bit in SQL?

  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Use bit in SQL?

    Hello,

    I have a Table with some char(1) columns. Allowed values are y,n and NULL. I consider to change to bit. My professor told me not to do, because of some kind of incompatibility. Did'nt really get what he meant. Is that true? Are there any issues about this?

    Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one incompatibility is that it is not portable

    some database systems do not support a BIT datatype, but they all support CHAR

    in other words, if you build a database using BIT, and then you wish to move it to some other system, you may have to change BIT anyway

    CHAR(1) and TINYINT are the two datatypes most often used

    another benefit of CHAR(1) and TINYINT is flexibility and this is illustrated by the following scenario

    imagine we have a BIT column called MaritalStatus, with values 0 and 1 meaning Single and Married

    actually, we wouldn't, because that would be misusing the BIT datatype, which is really supposed to be used like a switch -- on/off, yes/no, 0/1

    so we would have to name our BIT column IsMarried

    now, would we also have another column called IsSingle? probably not, but you can imagine the programming code either way...
    Code:
      IF IsMarried <> 0 ...
      IF IsSingle IS NOT TRUE ...
      IF NOT IsMarried ...
      IF IsSingle < 1 ...
      IF IsMarried = 1 ...
    and then one day, we are told to change our application to incorporate divorced as a valid marital status

    if we had started out with TINYINT values of 0 and 1, we could simply add 2

    would any of the code need to be changed? yes, if when we wrote the code we pretended that there would never ever forever never be more than two values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Thanks for the example. Do you think it makes sense to convert from y/n to 1/0 ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by silas
    Thanks for the example. Do you think it makes sense to convert from y/n to 1/0 ?
    no, i would just leave your CHAR(1) the way it is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    TINYINT is not standardized SQL either. So that is also not portable.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are absolutely right

    so what do you use for this, SMALLINT?

    or CHAR(1)?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm in favor of CHAR(1) for many reasons, but the problem is that most people use mneumonic values like "Y" for yes and "N" for no, and those values are very locale/language specific. You also run into problems where you have mneumonic conflicts, where you'd have SINGLE, MARRIED, DIVORCED, STUPID (remarried) where the obvious answer causes a mneumonic clash (the S for single and the S for stupid conflict).

    I have a very strong tendancy to use a foreign key to a lookup table for lookups like this... That allows me to validate the incoming data (so that the code can only insert values in the lookup table), and to extend the set of valid values by simply adding a new row to the lookup table. If you choose to get fancy about things, you can carry the lookup to a language/lookup table to allow you to store multiple language descriptions (via a one to many relationship) for your lookup values.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ...where the obvious answer causes a mneumonic clash (the S for single and the S for stupid conflict).
    it's even worse in my case: STUPID SQUARED (married twice, divorced twice)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    so what do you use for this, SMALLINT?
    Yes, SMALLINT is in SQL:2003.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    fascinating, thanks

    do you happen to have copies of the standard(s)? i personally don't, and i would enjoy knowing someone who does

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, I do. I just can't pass them along for copyright reasons.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    i wasn't asking for copies -- i just wanted to know whom to contact when i have a particular question about the standard (which is not often)

    for example, which of the following is valid ...

    select *, 23 from t

    select t.*, 23 from t

    select 23, t.* from t
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    1 is invalid. An <asterisk> must either be qualified (as in 2 and 3), or it must be the only expression in the <select list>. 2 and 3 are fine. (Subclause 7.23, <query expression> in SQL:2003)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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