4

I am looking for some hints or tricks for this design challenge I am up against:

I have a need to bitmask two of the same size varbinary fields stored in the database. Just to clarify, no this is not a "permissions table" or anything. I would normalize the database for those kinds of items and this data cannot be normalized. I am storing a varbinary field for a dynamic storage of bits that our application generates. I am also searching against these bits with another varbinary query. In .net, I use a BigInteger type to handle all of the bitmasking and it does a great job however I am thinking about offloading this process to the database server to filter the results before passing it back the caller. Right now, I support up to a varbinary(512) field type and will want to do both AND and OR operators and be able to compare if ANY (OR) or ALL (AND) of the bits in the right hand side operator made it through. MSDN says that you can use a varbinary() with a right hand side operator max bigint, however I need to exceed that.

A nice resource I have found is Adam Machanic's blog posting on this topic but looking to see if there are other approaches before I dig into it.

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/bitmask-handling-part-3-logical-operators.aspx

This would be for SQL 2005 or higher. If 2008 has an function not found in 2005, I would be fine with that, I just have not been able to identify one yet.

Any hints or tricks are greatly appreciated.

Thanks.

Travis Whidden

TravisWhidden
  • 2,142
  • 1
  • 19
  • 43
  • 1
    I am still curious about this answer, but for now I created a CLR function in SQL to do the bit masking operations that I wanted to test. – TravisWhidden Jan 23 '12 at 19:10
  • 2
    Storing bitmasks in SQL Server as a field where you want to filter on is not the greatest design as it is a search which can not be indexed. (so a table scan for every search). Personally I would offload the matching to an application server and use the db server for queries that can be optimized by the query governor. – Filip De Vos Feb 03 '12 at 12:58
  • If BigInt works in .NET then why is BigInt not big enough in SQL. – paparazzo May 11 '12 at 21:29
  • 1
    From my experiences with bitmasking in SQL Server, SQL has no good way of handling it and performance is always going to be inferior. You're better off using the simple CLR function you've created if you absolutely must check the bitmask but you'll never get efficient searching off the bitmask since as Filip says, it cannot be indexed. – Zhenny May 24 '12 at 23:36
  • Yea, I ended up doing a CLR type for it. It actually was very fast and acceptable. – TravisWhidden Jan 24 '13 at 04:13

1 Answers1

0

If you know the max length of the bitmap, you COULD spread it over multiple columns and mask them individually.

It gets messy real fast though.

Thomas Kejser
  • 1,264
  • 1
  • 10
  • 30