IP Addresses as int

[Niv3k]

Okay, we all know an IP address is four bytes, delimited by '.' I have a stored procedure that searches through an IP List with the format of *.10.100.1, where the * can be for any of the bytes.

The only problem is when I store the IP addresses as int, I run into problems with IP's > 128.x.x.x (because it is stored as a negative number in the int field)

I resolved this problem by using a bigint field, but I want to use just an int, because I'm using twice as much memory as I need.

In SQL 2000, is there any way to make it an unsigned int? Or can I convert a user entered 128 to -127, or whatever it should be? Or should I use a varbinary field instead...

Kevin

 

[harryrich]

I don't know enough about the varbinary to advise you on it. And, I don't know any way to get SQL Server to use unsigned integers. But, I advise against converting to 4 byte signed integer unless you have serious storage problems. You can easily lose more speed running things through conversions than you would by accessing the 4 extra bytes per record. And, you will complicate your software.

If you are only dealing in a million IP's, you're talking about only 8MB of memory, including an index, which is a noise level on today's disks, and not even a big deal with respect to holding the data in the processor.

You'll have to run you actual numbers, but I suspect you should look at other areas for optimization and come back to this problem when you're scraping the bottom of the barrel.

 

[Niv3k]

Thanks, it's kind of what I figured, but I was hoping... I had to forgo a lot of normalization due to the network guys who use this db not caring about that and wanting this comment field and that, and it wasted a lot of space. I think I'll just have to split the servers (IIS and SQL are running on the same box) if it ends up having to many performance issues.

Again, thanks!
Kevin