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