tdcheek (8/19/2009)
I've always liked storing IP's as binary(4)
I had considered using binary(4) however since bitwise operations cannot be performed on binary, this made integer my final choice.
HansVE (8/19/2009)
1. You do not know of subnetting?
Subnetting can easily be achieved using bitmasking on integer IPs. The following will yield 192.168.1.0 :
SELECT [dbo].[IPAddressToString]([dbo].[IPAddressToInteger]('192.168.1.1') & [dbo].[IPAddressToInteger]('255.255.255.0'))
HansVE (8/19/2009)
A "ip_int_to_ip_string computed column" will impede performance, as will the functions in the article.
Although using multiple tinyint fields requires no computational conversion, it does require that every ORDER BY, UNION, WHERE, etc operation be done four times, whilst a single integer field requires only one operation in all instances.
Also, if you wish to submit your IP address as a varchar(15) to the stored procedure to be then stored as four tinyint field within the table, you will require the same string splitting conversion as a single integer. Selecting rows to return IP addresses as varchar(15) will also require similar conversions. The only computational difference is mostly the avoidance of a rather efficient bitmask operation, which in my opinion, far outweights the costs of multiple operations when joining, filtering or ordering tables by IP addresses stored as four tinyint fields.