Andrew (8/19/2009)

---

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

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

Andrew

tdcheek (8/19/2009)

---

I've always liked storing IP's as binary(4)

HansVE (8/19/2009)

---

1. You do not know of subnetting?

SELECT [dbo].[IPAddressToString]([dbo].[IPAddressToInteger]('192.168.1.1') & [dbo].[IPAddressToInteger]('255.255.255.0'))

HansVE (8/19/2009)

---

A "ip_int_to_ip_string computed column" will impede performance, as will the functions in the article.

TheSQLGuru (8/19/2009)

---

could you please redo your testing using a char(17) datatype instead of a varchar(17)?

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.

Jean-Sebastien Carle (8/19/2009)

---

I'm not sure what would be the point since IP addresses (when used as strings) are not fixed length.

rja.carnegie (8/19/2009)

---

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.

TheSQLGuru (8/19/2009)

---

The point would be exactly what your article was about - what offers the best performance. There is a chance that fixed storage will perform better (however slightly just like parsename as opposed to substrings). It should be very easy to modify your existing test suite to try this option out. It wasn't like I was asking you to completely reinvent the wheel. :)

Jean-Sebastien Carle (8/19/2009)

---

Right. However, char(15) introduces a new element, trailing whitespace. Wouldn't you consider that as a reason to negate performance benefits char(15) could bring over varchar(15)?