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:
| Comparison Table | ||||||
|---|---|---|---|---|---|---|
| Datatype | Storage | Writing | Displaying | Sorting | Drawback(s) | Benefit(s) |
| varchar(15) | 15 b | Verbatim | Verbatim | Parse needed | Highest disk space needed | Readability, ease to insert |
| four tinyints | 4 b | Parse and one CAST | View or computed column | Native | Sew together with cast to recreate string | Ideal storage size, sorting, performance |
| four binary(1)s | 4 b | Parse and two CASTs | View or computed column | Requires two casts to sort and read, code is harder to read/maintain | Ideal storage size, obscure values | Obfuscate the data |
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.
Biography
Brent Shaub has been creating, renovating and administering databases since 1998. He’s worked on nearly two dozen professional systems, most incorporating web front-ends, iterating many times over the SDLC. After 14 years in the corporate world working for Foster Wheeler, Design Strategy and Research Pharmaceutical Services, Brent has created his own company, Buoyant Creations LLC, to provide health education.
Community Activity
Brent’s role as technical lead on his company’s flagship site Library of Learning, is to create the first web-based, database-driven Feldenkrais search tool, complete with recordings. The global endeavor will use insights from 30 senior practitioners and serve over 2,000 practitioners and their students. Brent has worked with the Australian Feldenkrais Guild on their website and teaches Feldenkrais classes.
SQL Server Interests
Open to alternative viewpoints, Brent encourages constructive debate in his articles on MSSQLTips.com. Impressed with the elegance of clear design, Brent digs into structural relationships. He’s a whiz at redesigning existing schemas and creating systems from ideas. Through fine-tuning logical differences organizing data, Brent aims to minimize the ripple-effect of
So many to say and so shot of time. 🤩 So this is not my 2 cents but 1/5 cent:
(1) Instead of “parsed using either SUBSTRING or CHARINDEX”, you can simply use the build-in function PARSENAME, which is perfect for IP4.
(2) You did not take into consideration that some users will have IP6.
(3) You mentioned the option to use varchar(15), four tinyint or four binary(1). What about using a single BIGINT?
(4) **Moreover**, what about simply using the IP4 original value, which is 4 Bytes value?
IPv4 uses a 32-bit. You are speaking the text representation of the IP4, and work hard on thinking how to store it using converting to different types instead of simply store the IP4 using the original value meaning a single BINARY(4)