TSQL TestBits function for rapidly testing multiple switch settings

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Comments posted to this topic are about the item TSQL TestBits function for rapidly testing multiple switch settings

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • Jeff Moden

    SSC Guru

    Points: 996832

    I don't know why someone rated this as a "3" at the start. It's a good primer on Boolean Logic even if someone doesn't use the function. Nicely done.

    --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.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Thank you, Jeff. I appreciate it.

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • Andy DBA

    SSCommitted

    Points: 1776

    I agree with Jeff, it is a good primer on boolean logic. From the title I thought the article would be about the bit data type though, so I was a "bit" surprised that it wasn't mentioned. 😀

    BOL touts the fact that the database engine optimizes the storage of multiple bit columns. I think it would be interesting to see a discussion comparing performance and clarity of syntax of the article's approach to an approach using bit columns.

    For example, if the article's switches were columns with bit datatypes, I believe the same result could be achieved with something like:

    select

    case

    when stopMail | lockDoors = 0 then 0

    when stopMail & lockDoors = 1 then 1

    else NULL

    end as HouseItems,

    select

    case

    when checkTires | stopMail | lockDoors | tellNeighbors = 0 then 0

    when checkTires & stopMail & lockDoors & tellNeighbors = 1 then 1

    else NULL

    end as AllItems

    from --rest of query

    This looks messier in the select statement than the function calls do, but there are no bit position variables, mask variables, or any set-up code. Also, this is less burdensome on the developer because they don't have to keep track of bit positions (or maintain them when they change!). And, according to BOL, you get to use all 8 bits in each byte as switches, so it's a slightly more efficient use of storage space.

    Finally, I admit this is a nit-picky comment, but I'm not sure why the examples are ORing with 0 to set the mask variables.

    SET @comboHouse

    = [highlight]0[/highlight] | @stopMail | @lockDoors -- 6

    SET @comboAll

    = [highlight]0[/highlight] | @checkTires | @stopMail

    | @lockDoors | @tellNeighbors -- 15

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Thanks for your post, Andy. I think yours is a nice, clean approach too. You're right, I didn't need the zeroes when initializing the masks. The zeroes were just a security blanket I guess. 🙂

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • gitmo

    Old Hand

    Points: 379

    Excellent article, Chuck. I went from zero knowledge in this to being confident I can apply boolean logic without a long list of case statements. Very well written.

  • Alex Fekken

    Ten Centuries

    Points: 1109

    Perhaps the zeroes are a reminder that in many cases you would want to use @comboHouse (or whatever it is that holds the flags, a column perhaps) instead of 0 to set the flags without affecting any existing ones.

  • Paul White

    SSC Guru

    Points: 150442

    Jeff Moden (4/9/2012)


    I don't know why someone rated this as a "3" at the start. It's a good primer on Boolean Logic even if someone doesn't use the function. Nicely done.

    I can't say for sure (because it wasn't me) but one guess might be that stuffing bits in this way is pretty widely regarded as an anti-pattern for SQL Server coding. It's a different story in compiled languages (such as .NET) of course, but bit-stuffing is hard to reconcile with relational principles (most would regard a bit-stuffed column as violating first normal form) and, as a consequence, does not work well with the SQL Server in general, and the query optimizer in particular (which is also based on relational principles). As an example, consider the statistics on a bit-stuffed column! Wrapping the whole thing in a scalar UDF (the very worst kind) may also have been a factor in the low rating.

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    There are FOUR spams, all worded the same.

    1282864, 1282865, 1282866

    1282868

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

Viewing 9 posts - 1 through 9 (of 9 total)

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