THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Correction on bitmask handling

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)
FROM BitmaskNumbers
WHERE (SUBSTRING(@Bitmask, DATALENGTH(@Bitmask) - Byte + 1, 1) & BitValue) = BitValue
AND Byte <= DATALENGTH(@Bitmask)

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

Published Wednesday, July 12, 2006 10:26 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Adam Machanic said:

Posting the first part of my series on bitmasks (yes, this is now officially a series) taught me a lot

January 25, 2009 2:47 PM

Eric said:

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!

February 5, 2012 5:06 AM

Adam Machanic said:

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?


February 5, 2012 12:58 PM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement