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