Home Uncategorized Correction on bitmask handling

    Correction on bitmask handling

    By
    Adam Machanic
    -
    431
    2

    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: 

    0x01 != 0x0001

    … and that is not good!

    So here’s a corrected version that fixes the problem: 

    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

    More to come soon… Bit shifting, logical operators, and other fun ways to annoy this guy:

    Previous articleDealing with very large bitmasks
    Next articleBitmask Handling, part 2: Bitmask reconstitution
    Adam Machanic
    http://dataeducation.com
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    RELATED ARTICLESMORE FROM AUTHOR

    2 COMMENTS

    1. 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!

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

    Comments are closed.