• lhowe (8/19/2009)


    Darn it HansVE, you beat me to the punch! I was going to say exactly the same thing. I don't see why someone would go to all of the trouble to convert IPv4 values this way, and back again, when it's far easier just to store them as four tinyint values.

    There could be a performance benefit. My own tests a while back indicated that one int comparison was equally efficient as smallint or tinyint, probably because you're using a computer with 32-bit registers. The bit datatype stunk - but this was on SQL Server 2000 or maybe even 7. Still, I prefer to put bit in tinyint.

    I agree however that four octets is useful in many ways. There's nothing to stop you storing an int or binary(4) version as well.

    I spent a little while devising an encoding of 6 character alphanumeric strings (09AZ) into int, for an indexed key field. I got slightly poorer performance with int than with char(6) even not counting the cost of conversion, which wasn't what I expected. I have increased my knowledge of indexing since, too (apparently non-declared-unique indexes have 8 bytes arbitrarily added to the key??) Perhaps I got it wrong.

    Anyway, maybe we should be thinking about IPv6, where the address is, what, 16 bytes? Plus refinements that the Wikipedia page hasn't yet communicated to me. 😉 Binary(16) for that, I think. Although IPv4 is not going away quickly yet.