Tom.Thomson (10/28/2009)

---

Yes, of course we have to have some negative values. But I would be much happier if the integer stored actually consisted of the 32 bit IP address, instead of some bizarre different bit pattern, and the conversions in the article do not achieve that. If we are going to pass this IP address to some low leveldata comms software, we need the number to be negative when the relevant octet is above 127, and positive when it is under 128 -not the other way round. The dotted notation 1.1.1.1 represents the 32 bit value 0x01010101, not 0x81010101, and it is not useful to store it as the latter instead of the former. Of course it's trivial to alter the conversion to do the right thing.

I think this nicely illustrates that a little knowledge is a dangerous thing - it shows us what might happen when the DBA doesn't really understand what the data is but acts on his misunderstqanding: with the approach in the article if some application component decides it wants the 32-bit IP address it has to do an extra conversion, because the database is providing only varchar and the wrong 32-bit pattern; that will probably destroy efficiency at some point in the future. It's much better if the DBA finds out what the external world uses and follows suit (in this case, uses the correct 32 bit pattern - which probably ought to be described as varbinary(4) rather than int, since IP protocol works in terms of streams of octets, not integers). Of course there will be casses where what the external world uses will be hopelessly ineficient - but that certainly isn't the case here: be under no illusion that any serious communications software anywhere uses the varbinary(15) representation internally, it's just a device for the human interface, internally the representation is 4 octets.