What is the equivalent to Yes/No data type in SQL?

  • ed-1075072

    Mr or Mrs. 500

    Points: 596

    Hi everyone!

    Which SQL data type is equivalent to Yes/No [ms access]?

    I am new to SQL[using sql express] and I am not sure whether BIT or Char data type is the best way to go to store booleans. I want to be able to use this column in queries.

    I want to store a boolean value in this column, whethre is Yes/No, True/False or 1/0.

    Thanks

    Ed

  • Lynn Pettis

    SSC Guru

    Points: 442117

    Depending on how many of these values you may have in a row, I'd consider using the bit data type.

  • ed-1075072

    Mr or Mrs. 500

    Points: 596

    Thanks Lynn,

    Bit data type was what I though of using in the first place, I just wanted to make sure I can use it in queries to check whether the value of the column is true/false or yes/no.

    I read a book [don't remember which one] where the author suggested to use Char(1) to store 'Y' or 'N'.

    Ed

  • Grant Fritchey

    SSC Guru

    Points: 395320

    You can also use a CHAR(1). A little more flexibility than bit, but then it has a little more flexibility than bit.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GSquared

    SSC Guru

    Points: 260824

    One problem I've run into with char(1) for this is that some clever developer somewhere along the way inevitably wants to put T/F in a column that also has Y/N, and even 1/0 (as characters). Suddenly, querying becomes really, really interesting. Check constraints can help with that.

    Also takes up more space (disk and RAM) than bit. (If you have 8 bit fields per row, that's the same amount of disk/RAM as 1 char(1) field.)

    In my old app that was Access on top of SQL, checkboxes worked with the bit data type, but not with Y/N or T/F char fields (of course), so another point becomes, will the front end app have to translate these, or will it deal with it more gracefully? Most app dev languages deal with 1/0 for that kind of thing more easily than with character data.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ed-1075072

    Mr or Mrs. 500

    Points: 596

    I do plan to use a check box control for this field in VB.NET

    Thanks to all for the valuable information.

    Ed

  • Grant Fritchey

    SSC Guru

    Points: 395320

    GSquared (6/4/2009)


    One problem I've run into with char(1) for this is that some clever developer somewhere along the way inevitably wants to put T/F in a column that also has Y/N, and even 1/0 (as characters). Suddenly, querying becomes really, really interesting. Check constraints can help with that.

    Also takes up more space (disk and RAM) than bit. (If you have 8 bit fields per row, that's the same amount of disk/RAM as 1 char(1) field.)

    In my old app that was Access on top of SQL, checkboxes worked with the bit data type, but not with Y/N or T/F char fields (of course), so another point becomes, will the front end app have to translate these, or will it deal with it more gracefully? Most app dev languages deal with 1/0 for that kind of thing more easily than with character data.

    True. That's why I said that the flexibility is the weakness as well as the strength. I prefer using the bit field myself, but I've been in situations where becuase of technology or politics (usually the latter) we've been unable to.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GSquared

    SSC Guru

    Points: 260824

    Yeah, I got what you meant on that one, Grant. Was just expanding on it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Grant Fritchey (6/4/2009)A little more flexibility than bit, but then it has a little more flexibility than bit.

    not sure if this is a circular reference or quantum-logic 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Grant Fritchey

    SSC Guru

    Points: 395320

    PaulB (6/5/2009)


    Grant Fritchey (6/4/2009)A little more flexibility than bit, but then it has a little more flexibility than bit.

    not sure if this is a circular reference or quantum-logic 😀

    Yes.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 993924

    Depending on what is really needed outside of that column, I'll sometimes make the column a TINYINT because you can aggregate TINYINT, but not BIT. Of course, same old story of "it depends". If you want the TINYINT to have the same restrictions as a BIT for values, you'd have to put a check constraint on the column to only accept values <= 1 (or <2 or whatever).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Tim Walker.

    SSCertifiable

    Points: 5173

    Nice discussion, but from the original question Yes/No, True/False, one state or the other state - has to be a bit data type doesn't it?

    The business questiion is - are there ever more than two values? And after thinking hard, if there are, you probably need a tinyint (one byte) data type. Im sure from the way the question is phrased you will not need a smallint (two byte) or bigger datatype.

    Tim

    .

  • Jeff Moden

    SSC Guru

    Points: 993924

    With that thought in mind, even the BIT datatype is tri-state... Yes, No, an dunno (NULL)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • ed-1075072

    Mr or Mrs. 500

    Points: 596

    Thank you all, It is great to hear other people's opinions, I learn a lot from others input.

    Thanks again.

    Ed

  • dmbaker

    SSCertifiable

    Points: 5015

    PaulB (6/5/2009)


    Grant Fritchey (6/4/2009)A little more flexibility than bit, but then it has a little more flexibility than bit.

    not sure if this is a circular reference or quantum-logic 😀

    Perhaps Mr. Fritchey works for the Department of Redundancy Department?

    🙂

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply