Jean-Sebastien Carle (10/28/2009)
Also, the choice between varbinary(4) (which should really be binary(4)) and int comes down to deciding between being able to natively bitmask IPs with subnet from within SQL Server or not. The reason I chose int regardless of the shift into negative is that I can write a stored procedure to retrieve all IPs of a given subnet, which could not be possible with binary(4) and would require me to retrieve the full list and then bitmask filter it in the application layer.
Yes, it should be binary(4) not varbinary(4). But I don't understand at all why you think I can't do subnet checks in SQL using binary(4) or, for that matter, the 4 tinyints representation.
The check for whether address a occurs in the subnet base b mask m is quite simple:
((a XOR b) AND m) = 0
which is easy to translate into SQL - I guess we agree on that?
(Of course if all three values are ints, and wre produced from the varchar(15) representation according to your scheme, this doesn't work! To make it work you would have to have converted the mask using an algorithm that would deliver an IP address from the varchar(15) representation instead of a different integer, so your system would have to end up having to have both conversions in it).
If a is 4 tinyints a1 a2 a3 a4 and the base and mask values are similarly given in small parts, the check is
(((a1 XOR b1) AND m1) = 0) and
(((a2 XOR b2) AND m2) = 0) and
(((a3 XOR b3) AND m3) = 0) and
(((a3 XOR b3) AND m3) = 0)
This too is easy to convert to SQL.
If a is an array of 4 octets, and the other values also, the test is
(((a[1] XOR b[1]) AND m[1] = 0) and
...etcetera
which is also trivial to turn into SQL. But it isn't even neccessary to worry about the 4 components if you are using binary(4) to represent the array of 4 octets, since bitwise XOR and AND are directly applicable to binary(4) values (see for example http://msdn.microsoft.com/en-us/library/ms190277%28SQL.105%29.aspx) - the only difference from the integer case is that you might write 0x00000000 instead of 0.
So why can you not do it in SQL, why do you have to break out into the application layer?
Tom