In MSSQL its possible to convert an ip address on the fly so that it makes it possible to do ip range search on text based ip addresses. I found out about this on anotherposted article. However it has a little issue if you want to scale.

However i did't like the method that was used. As its doing the compares it is extremly slow to search on calculated values all the time. So i came up with the following functions. I dropped the use of the PARSENAME function because it isn't deterministic. In these functions you can create a pre computed field on existing data on the function, add an index, then perform the ip range query using index seek's vs an index scan. Or you could just simply convert the ip address into a bigint and store it into the table instead, this would save a lot of space on larger table's.

CREATE FUNCTION [dbo].[IPToBINT] (@ip varchar(max))
        RETURNS bigint
        WITH SCHEMABINDING
AS
BEGIN
        DECLARE @idx1 int
        DECLARE @idx2 int
        DECLARE @idx3 int
        DECLARE @idx4 int
        DECLARE @ret bigint

        SELECT @idx1 = CHARINDEX('.', @ip)
        SELECT @idx2 = CHARINDEX('.', @ip, @idx1+1);
        SELECT @idx3 = CHARINDEX('.', @ip, @idx2+1);

        SELECT @ret = CONVERT(bigint, SUBSTRING(@ip, 0, @idx1)) * POWER(2, 24) +
                                        CONVERT(bigint, SUBSTRING(@ip, @idx1 + 1, @idx2 - @idx1 - 1)) * POWER(2, 16) +
                                        CONVERT(bigint, SUBSTRING(@ip, @idx2 + 1, @idx3 - @idx2 - 1)) * POWER(2, 8) +
                                        CONVERT(bigint, SUBSTRING(@ip, @idx3 + 1, LEN(@ip) - @idx3))
        RETURN @ret
END
GO

CREATE FUNCTION [dbo].[BINTToIP] (@ip bigint)
        RETURNS varchar(16)
BEGIN
        DECLARE @ret varchar(16)
SELECT
        @ret =  CONVERT(varchar, @ip / POWER(2, 24)) + '.' +
                                CONVERT(varchar, @ip  / POWER(2, 16) & 0xFF) + '.' +
                                CONVERT(varchar, @ip / POWER(2, 8) & 0xFF) + '.' +
                                CONVERT(varchar, @ip & 0xFF)
        RETURN @ret
END
GO