Question: What datatype should I choose for storing an IP Address in a SQL Server?

By selecting the right datatype would it be easy enough to filter by IP address then?

Answer: 
As I want to handle both `IPv4` and `IPv6`, I am using `VARBINARY(16)` and the following `SQL CLR` functions to convert the `text` IP address presentation to bytes and the reverse:

```
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
    IPAddress IP;

    if (IPAddress.TryParse(value.Value, out IP))
    {
        return new SqlBytes(IP.GetAddressBytes());
    }
    else
    {
        return new SqlBytes();
    }
}

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
    string output;

    if (value.IsNull)
    {
        output = "";
    }
    else
    {
        IPAddress IP = new IPAddress(value.Value);
        output = IP.ToString();
    }

    return new SqlString(output);
}

```
Thanks RBarry. I'm putting together an IP block allocation system and storing as binary is the only way to go. 

I'm storing the CIDR representation (ex: 192.168.1.0/24) of the IP block in a varchar field, and using 2 calculated fields to hold the binary form of the start and end of the block. From there, I can run fast queries to see if a given block as already been allocated or is free to assign. 

I modified your function to calculate the ending IP Address like so:

```
CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)
    DECLARE @ip AS VARCHAR(15)
    DECLARE @size AS INT

    SELECT @ip = Left(@block, Len(@block)-3)
    SELECT @size = Right(@block, 2)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
    RETURN @bin
END;
go

```