In the article on handling bitmasks I posted the other day, I made a fatal error in the splitBitmask function. The function treated the low byte as the first byte, instead of the high byte. Therefore:
[sql]0x01 != 0x0001
[/sql]
… and that is not good!
So here’s a corrected version that fixes the problem:
[sql]CREATE FUNCTION dbo.splitBitmask
(
@Bitmask VARBINARY(4096)
)
RETURNS TABLE
AS
RETURN
(
SELECT Number
FROM BitmaskNumbers
WHERE (SUBSTRING(@Bitmask, DATALENGTH(@Bitmask) – Byte + 1, 1) & BitValue) = BitValue
AND Byte <= DATALENGTH(@Bitmask)
)
GO
[/sql]
More to come soon… Bit shifting, logical operators, and other fun ways to annoy this guy:
I read your article and it really came in handy, as this is unfortunately precisely what I needed to do for a project of mine. I have two questions though. I deal with large sets of data where I have to query against, and the change from using a long and the & power method versus dealing with a function to determine which bit is set in the varbinary has been a big hit performance-wise. Do you have any ideas for this?
Also, do you deal with C#? It appears when I would read or write the varbinary to C# it got it backwards, like your original suggestion on how to read the bits in the varbinary.
Thanks for your help!
Hi Eric,
Yes, this is much, much, much slower than using bigint and bitwise methods. Which makes perfect sense, since those operations can often be handled in a single processor operation, whereas with my expanded methods you’ll wind up with many, many, many operations (hundreds? thousands? depends on how big your bitmask is and other issues).
Making things much worse is the fact that this is all being done in SQL — which is very slow with these kinds of things. My suggestion to you would be to utilize SQLCLR, but it sounds like you’re already attempting that.
I’m not sure what you mean about getting the values backward — that’s never happened to be before (I don’t think). But if it’s happening that way consistently, can’t you simply reverse the logic?
–Adam