Jeff Moden (1/18/2016)
Eirikur Eiriksson (1/18/2016)
Network endian-ness is predominantly big-endian and the method NetworkToHostOrder will swap the byte order to small-endian. This tells me that you are storing the IPs as big-endian bigint with the first four bytes set to zero which makes sense as otherwise they could not be sorted.
I guess I'm missing something here, Eirikur. If you remove the dots from a properly formed IP address (each octet containing 3 display digits) and convert that string to an Integer value, you still end up with a 12 digit Integer value. A 4 digit Integer is only capable of a 10 digit value and, of course, the leading digit will not exceed "1". What conversion are you using where the first 4 bytes of a big endian integer would all be zeroed out for a 12 digit IP address that's been converted to an Integer value?
Slight correction on the math here, unsigned each byte stores values from 0x00 to 0xFF or 0 to
256 255 decimal, that decimal is 3 digits and we have four of those bytes in an integer hence 12 digits.
Simplest mean of conversion in T-SQL is simply to substring and prefix with the padding, here is a pseudo snip
0x00000000 + SUBSTRING(CONVERT(BINARY(4),IA.FAKE_IP,0),4,1) +
SUBSTRING(CONVERT(BINARY(4),IA.FAKE_IP,0),1,1) AS IP_BINTREV