Optimal Storage of IP addresses in a SQL Server database

Problem

Often databases are used with web-based interfaces and recording the IP address of the end user can assist with debugging, marketing, bandwidth planning and collation selection to name a few. In a scenario where each page access is logged, is there an optimal way to store IP addresses in SQL Server?

Solution

This tutorial will work through different ways (two obvious and one obscure one) to store IP addresses in your SQL Server database.

IP Address Storage Option #1 – varchar(15)

The first option is using a varchar(15):

• From CGI variables, the four octets are separated with periods (.) which form a string.
• Writing this string as a varchar to the database is straight-forward.
• The simplicity has some tradeoffs.
  • Some space is under-utilized whenever an octet is less than 100, and the mask always uses three bytes. (101.100.99.9 uses 12 characters.)
  • Octets need to parsed using either SUBSTRING or CHARINDEX to sort and search by geographic location.

IP Address Storage Option #2 – Four tinyints

The second option is using four tinyints:

• Octets range between 0 and 255 making them perfect matches for tinyints and therefore storage space is perfectly utilized.
• Some parsing will need to be done when writing the data, but the benefits are many.
  • Because they are numbers, sorting will work as expected.
  • A clustered index can be created on all four fields to improve performance.

A view can be created to display the original string…

<div class="codediv">
<pre>CREATE VIEW V_IP_ADDRESS AS<br>
SELECT IP_1, IP_2, IP_3, IP_4,<br>
 CAST(IP_1) AS VARCHAR + '.' +<br>
 CAST(IP_2) AS VARCHAR + '.' +<br>
 CAST(IP_3) AS VARCHAR + '.' +<br>
 CAST(IP_4) AS VARCHAR<br>
 AS IP_ALL<br>
FROM IP_ADDRESS</pre>
</div>

…as can a computed column.

<div class="codediv">
<pre>CREATE TABLE IP_ADDRESS (<br>
 IP_1 TINYINT NOT NULL,<br>
 IP_2 TINYINT NOT NULL,<br>
 IP_3 TINYINT NOT NULL,<br>
 IP_4 TINYINT NOT NULL,<br>
 IP_ALL AS<br>
 CAST(IP_1 AS VARCHAR(3)) + '.' +<br>
 CAST(IP_2 AS VARCHAR(3)) + '.' +<br>
 CAST(IP_3 AS VARCHAR(3)) + '.' +<br>
 CAST(IP_4 AS VARCHAR(3))<br>
 PERSISTED,<br>
 CONSTRAINT PK_IP PRIMARY KEY (IP_1, IP_2, IP_3, IP_4)<br>
)</pre>
</div>

Note that this example creates a clustered index on the four IP octets immediately. Another option is an autonumber Primary Key (PK) to start, then dropping that and creating a clustered index on the IP segments later.

IP Address Storage Option #3 – Four binary(1)s

The third option is using four binary(1)s:

• For completeness, four binary(1)s use the same amount of disk space as four tinyints, but their use is more complex.
• Writing will require parsing and casting.
  • To write, each octet needs to be parsed and casted [to 0x__] using this function master.dbo.fn_varbintohexstr(): master.dbo.fn_varbintohexstr(cast (IP_1 as varbinary)).
  • A computed column or view will need to cast to tinyint then varchar to return the original address.
• The main benefit of this approach is to obfuscate anyone viewing the database directly, and chances are if they are viewing your data they can probably also read hexadecimal.

<div class="codediv">
<pre>CREATE TABLE IP_ADDRESS_BIN (<br>
 IP_1 binary(1) NOT NULL,<br>
 IP_2 binary(1) NOT NULL,<br>
 IP_3 binary(1) NOT NULL,<br>
 IP_4 binary(1) NOT NULL,<br>
 IP_ALL AS<br>
  CAST(cast(IP_1 as tinyint) AS VARCHAR(3)) + '.' +<br>
  CAST(cast(IP_2 as tinyint) AS VARCHAR(3)) + '.' +<br>
  CAST(cast(IP_3 as tinyint) AS VARCHAR(3)) + '.' +<br>
  CAST(cast(IP_4 as tinyint) AS VARCHAR(3))<br>
 PERSISTED,<br>
 CONSTRAINT PK_IP_BIN PRIMARY KEY (IP_1, IP_2, IP_3, IP_4)<br>
)</pre>
</div>

Here is a comparison of the three options outlined above:

Next Steps

• Consider migrating IP addresses into their own table with an autonumber PK and link using foreign keys; this will save space.
• Read about VIEWs and COMPUTED COLUMNs.