IP Address storage in Microsoft SQL

Work has been hectic for the last couple of weeks. I am working on creating one database while updating/tweaking another one. The database I’m developing isn’t causing me any problems because it’s pretty straight forward. The other one, though, is causing me a lot of headaches.

One of the improvements I’m adding to the second database is the ability to view all IP addresses available at any given location to help with inserting static IP use records. Sounds easy enough until you consider that I don’t store every possible IP address for the locations. There are 30+ locations and each location IP range supports over 1,000 IP’s (Class B subnet). Because each location uses less than 100 static IP’s, I don’t see a reason to store all those IP’s without any correlating data.

The initial database creation only had two tables for IP storage: location IP range & equipment (computer, printer, etc.) static IP mapping. The location IP ranges table holds the location ID (details are in another table) and the individual IP octets. That means a range like 192.168.1.1 – 192.168.5.255 uses eight (8) columns to store the range. That was the first tweak, breaking down the IP addresses already stored and storing the individual octets.

I know it sounds odd to not store the IP as a whole, i.e. 192.168.1.1, but there are a couple of good reasons. One is storage space and the other is the ability to perform arithmetic operations against the IP without having to parse it using PARSENAME. The second reason came in really handy for the stored procedure showing all possible IP addresses for the locations.

The storage space savings isn’t huge but it can add up later. I found an article showing why it’s better to store the IP octets individually in tinyint columns. Tinyint works well for storing IP octets because it only supports numerical values from 0 to 255. Storing the IP this way also helps if I want to index the octet columns (combined and/or individual) to make searches faster.

Another plus is sorting the data returned. While stored as a whole IP, sorting the returned record set can cause unexpected outcomes (i.e. .100 before .20). Storing the individual octets allows me to sort the data by octet three and four (respectively) to get the record set sorted in the proper order.

Storing the IP addresses as individual octets can cause a problems. One of the problems is it can complicates queries. Queries to find the full IP address have to concatenate the cell values. So, if 192.168.1.1 is stored as individual octets and you want to pull the IP back with the decimal point, the query would look something like this:

SELECT CAST(Octet1 AS VARCHAR(4)) + '.' + CAST(Octet2 AS VARCHAR(4)) + '.' + CAST(Octet3 AS VARCHAR(4)) + '.' + CAST(Octet4 AS VARCHAR(4)) AS IPAddress FROM IPTable

The octets have to be explicitly converted or SQL will try to create a decimal number with the concatenation. The I/O cost to cast tinyint to varchar is minimal.

I’ll post an example of the stored procedure to show all possible location IP addresses on the next post. The stored procedure will not only return all of the IP addresses in the range but it will also show information about any equipment using a static IP address (or nothing if the address is free).

Men do not quit playing because they grow old; they grow old because they quit playing. – Oliver Wendell Holmes

21.Aug.07 Microsoft SQL