nicholastjh (8/19/2009)
Nice article. Very informative.But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.
For example: 127.0.0.1 would result in -16777215.
That's the whole point. Some IP addresses will map to negative integers. As there are 2^32 IP Addresses and 2^31 non-negative integers in SQL Server we have to use positive as well as negative values.
The idea with four columns - while not bad - has some flaws:
1. There is no merits with 1NF as IP address is a complete being. Would you store postal code with few char(1) columns?
2. If you want to perform table wide bit operation with mask (mask may be from 0 to 32 actually) you will have to either map four tinyints to one int the same way it is presented in the article or map it to bigint. Not good for performance :>
If you want readability - just use view with some ip_int_to_ip_string computed column.