How can I do a CIDR query on SQL Server Binary Column Type

Question

I have a need to store both IPv6 and IPv4 addresses in such a way they can be ordered and searched in a SQL Server DB more or less using CIDR notation. I'm intending to use binary(16), with IPv4 encoded as IPv4-mapped-IPv6 so there is one single consistent representation of any one IP address. I can derive the appropriate network masks in binary form to supply to the query. But SQL Server can only perform bitwise operations when one operand is a numeric. I apparently cannot do a bitwise '&' with a binary(16) and a 128 bit value.

I realize I can read each row and do this in code, but I need this search to happen in the DB since there are a few million IPs to search through. I've seen most of the posts on "storing" and converting between binary and text. But those don't address my question. My question is more around binary searching of binary(16).

What is the best way to effect a CIDR search over binary(16) fields in SQL Server?

Answer 1

Turns out I was not creating the binary ranges properly to perform the search. I'm using .NET IPAddress and BitArrayfor AND/OR bitwise operations. To create the proper CIDR mask for the network portion, I had to reverse the order of bits for each byte when setting them with BitArray. The reason is the bit positions are stored as least-significant-first with respect to each byte. So for example the bitwise position (1-128) of 9 for an address range is actually 16 when using BitArray. This is in fact documented in the API. Now I can compute the start and end ranges with prebuilt network masks and use those in a SQL between expression.