TSQL TestBits function for rapidly testing multiple switch settings

Question

TSQL TestBits function for rapidly testing multiple switch settings

Chuck Hoffman-291200

SSC Veteran

Points: 234
More actions
April 8, 2012 at 11:32 am

#258899

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
------------

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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.

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

Chuck Hoffman-291200 SSC Veteran Points: 234 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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: 150468 More actions · Answer 7

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Chuck Hoffman-291200 SSC Veteran Points: 234 More actions · Answer 8

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
------------