You missed one obvious way to store IP addresses, which is both the smallest data size and yet still easy to interpret as an IP address -- binary(4).

127.0.0.1 becomes 0x7F000001

I wrote the following functions, and hereby declare them into the public domain:

create function dbo.ConvertIPAddressToBinary (@IPAddress varchar(15))

returns binary(4)

as

begin

declare @Octet1 binary(1), @Octet2 binary(1), @Octet3 binary(1), @Octet4 binary(1);

select @Octet1 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));

select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));

select @Octet2 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));

select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));

select @Octet3 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));

select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));

select @Octet4 = cast(cast(@IPAddress as tinyint) as binary(1));

return @Octet1 + @Octet2 + @Octet3 + @Octet4;

end

go

create function dbo.ConvertBinaryToIPAddress (@IPAddress binary(4))

returns varchar(15)

as

begin

declare @BigIP bigint;

declare @Octet1 tinyint, @Octet2 tinyint, @Octet3 tinyint, @Octet4 tinyint;

select @BigIP = cast(@IPAddress as bigint);

select @Octet4 = @BigIP % 256;

select @BigIP = @BigIP / 256;

select @Octet3 = @BigIP % 256;

select @BigIP = @BigIP / 256;

select @Octet2 = @BigIP % 256;

select @BigIP = @BigIP / 256;

select @Octet1 = @BigIP % 256;

return cast(@Octet1 as varchar) + '.' + cast(@Octet2 as varchar) + '.' + cast(@Octet3 as varchar) + '.' + cast(@Octet4 as varchar);

end

go