Another use for a bitwise operator
I have just finished the code for a new transactional replication setup.
Requirements were to be able to filter rows according to the destinations that formed part of the fullfilment of the order. This could theoretically include up to 10 destinations however in reality it tended to be 2 or 3. The data would be real-time messages flowing out of the mainframe into the SQL Server via BizTalk.
The easiest method from the outset appeared to be to read the message in BizTalk and tag it according to where that message needed to go. As previously mentioned there were a number of possible destinations, so what would be an easy way to do this without introducing multiple redundant columns?
I decided on using a bitwise comparison of the tag to a predetermined flag within a lookup table. In other words, BizTalk checks the row; follows the business rules to decide which destinations should see the message; looks up the integer value for those destinations; calculates the bitwise product of those values; and tags the message with the product.
The logic works like this:-
- First we create a series of integers representing the positions of each bit (1,2,4,8,16,32,64,128 etc).
- This can be done by multiplying the value from the previous entry by 2.
- BizTalk uses the table we created above and finds the bitwise product for the required destination values (i.e. If required destinations above were Germany, France and Netherlands, BizTalk would give the product as 21)
- This can also be derived with a bitwise XOR operator (16^4^1)
We now have a single value that carries all the destinations that will see the record. Now we can think about the replication process itself.
So now we need to add a column to the replicated table which is going to hold the bitwise value. For my example we will call this [RowBitwiseFlag]. BizTalk will be populating this column as messages are inserted into my table.
In the replication wizard, or in your @FilterClause parameter in your sp_addarticle, sp_addarcticlefilter and sp_addarticleview scripts, you now need to put your filter criteria. For records destined for Netherlands for example, the @FilterClause parameter will look something like this:
@Filter_Clause = [RowBitwiseFlag] & 16 = 16
Set up your replication with this filter in place, and you now have a useful method of filtering out unwanted records.