• tdcheek (8/19/2009)


    I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:

    I like this format as well, since that's essentially what IP addresses are anyway. Like using INT and four separate TINYINT columns, it is efficient on space and has implicit validation. However, it has a few other advantages I like:

    1) The queries I perform are very simple and efficient. Most of them are sargable. You don't have the problem of half the address space being negative that you have with INT. It is very easy to say:

    WHERE @IPAddress BETWEEN RangeLowerLimit AND RangeUpperLimit

    2) When viewed in a query window, it is represented as a hexadecimal sequence. That makes it just as easy to compare two addresses visually as the dotted notation.

    3) It's relatively simple to convert to dotted notation if necessary.

    Andrew

    --Andrew