{"id":158,"date":"2014-10-11T10:52:38","date_gmt":"2014-10-11T10:52:38","guid":{"rendered":"http:\/\/www.codereview.co\/?p=158"},"modified":"2014-10-11T21:03:17","modified_gmt":"2014-10-11T21:03:17","slug":"storing-an-ip-address-in-a-sql-server-database","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/storing-an-ip-address-in-a-sql-server-database\/","title":{"rendered":"Storing an IP address in a SQL Server database"},"content":{"rendered":"

Scenario proposal<\/strong><\/span><\/p>\n

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?<\/span><\/p>\n

Solution<\/strong><\/span><\/p>\n

This tutorial will work through two obvious options and an obscure one on different ways to store IP addresses in your database.<\/span><\/p>\n

The first option is using a varchar(15)<\/strong>:<\/span><\/p>\n

\n
From CGI variables, the four octets are separated with periods (.) which form a string.<\/span><\/li>\n
Writing this string as a varchar to the database is straight-forward.<\/span><\/li>\n
The simplicity has some tradeoffs.<\/span>\n
\n
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.)<\/span><\/li>\n
Octets need to parsed using either SUBSTRING or CHARINDEX to sort and search by geographic location (see here).<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
The second option is using four tinyints<\/strong>:<\/span><\/p>\n
\n
Octets range between 0 and 255 making them perfect matches for tinyints and therefore storage space is perfectly utilized.<\/span><\/li>\n
Some parsing will need to be done when writing the data, but the benefits are many.<\/span>\n
\n
Because they are numbers, sorting will work as expected.<\/span><\/li>\n
A clustered index can be created on all four fields to improve performance.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
A view can be created to display the original string…<\/span><\/p>\n
CREATE VIEW V_IP_ADDRESS AS<\/em><\/span><\/p>\n
SELECT IP_1, IP_2, IP_3, IP_4,<\/em><\/span><\/p>\n
CAST(IP_1) AS VARCHAR + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_2) AS VARCHAR + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_3) AS VARCHAR + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_4) AS VARCHAR<\/em><\/span><\/p>\n
AS IP_ALL<\/em><\/span><\/p>\n
FROM IP_ADDRESS<\/em><\/span><\/p>\n
…as can a computed column.<\/span><\/p>\n
CREATE TABLE IP_ADDRESS (<\/em><\/span><\/p>\n
IP_1 TINYINT NOT NULL,<\/em><\/span><\/p>\n
IP_2 TINYINT NOT NULL,<\/em><\/span><\/p>\n
IP_3 TINYINT NOT NULL,<\/em><\/span><\/p>\n
IP_4 TINYINT NOT NULL,<\/em><\/span><\/p>\n
IP_ALL AS<\/em><\/span><\/p>\n
CAST(IP_1 AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_2 AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_3 AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(IP_4 AS VARCHAR(3))<\/em><\/span><\/p>\n
PERSISTED,<\/em><\/span><\/p>\n
CONSTRAINT PK_IP PRIMARY KEY (IP_1, IP_2, IP_3, IP_4)<\/em><\/span><\/p>\n
)<\/em><\/span><\/p>\n
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.<\/span><\/p>\n
The third option is using four binary(1)s<\/strong>:<\/span><\/p>\n
\n
For completeness, four binary(1)s use the same amount of disk space as four tinyints, but their use is more complex.<\/span><\/li>\n
Writing will require parsing and casting.<\/span>\n
\n
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)).<\/span><\/li>\n
A computed column or view will need to cast to tinyint then varchar to return the original address.<\/span><\/li>\n<\/ul>\n<\/li>\n
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.<\/span><\/li>\n<\/ul>\n
CREATE TABLE IP_ADDRESS_BIN (<\/em><\/span><\/p>\n
IP_1 binary(1) NOT NULL,<\/em><\/span><\/p>\n
IP_2 binary(1) NOT NULL,<\/em><\/span><\/p>\n
IP_3 binary(1) NOT NULL,<\/em><\/span><\/p>\n
IP_4 binary(1) NOT NULL,<\/em><\/span><\/p>\n
IP_ALL AS<\/em><\/span><\/p>\n
CAST(cast(IP_1 as tinyint) AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(cast(IP_2 as tinyint) AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(cast(IP_3 as tinyint) AS VARCHAR(3)) + ‘.’ +<\/em><\/span><\/p>\n
CAST(cast(IP_4 as tinyint) AS VARCHAR(3))<\/em><\/span><\/p>\n
PERSISTED,<\/em><\/span><\/p>\n
CONSTRAINT PK_IP_BIN PRIMARY KEY (IP_1, IP_2, IP_3, IP_4)<\/em><\/span><\/p>\n
)<\/em><\/span><\/p>\n
Here is a comparison of the three options outlined above:<\/span><\/p>\n\n\n\n\n\n\n\n\n
Comparison Table<\/strong><\/span><\/td>\n<\/tr>\n<\/thead>\n
Datatype<\/strong><\/span><\/td>\n Storage<\/strong><\/span><\/td>\n Writing<\/strong><\/span><\/td>\n Displaying<\/strong><\/span><\/td>\n Sorting<\/strong><\/span><\/td>\n Drawback(s)<\/strong><\/span><\/td>\n Benefit(s)<\/strong><\/span><\/td>\n<\/tr>\n
varchar(15)<\/span><\/td>\n 15 b<\/span><\/td>\n Verbatim<\/span><\/td>\n Verbatim<\/span><\/td>\n Parse needed<\/span><\/td>\n Highest disk space needed<\/span><\/td>\n Readability, ease to insert<\/span><\/td>\n<\/tr>\n
four tinyints<\/span><\/td>\n 4 b<\/span><\/td>\n Parse and one CAST<\/span><\/td>\n View or computed column<\/span><\/td>\n Native<\/span><\/td>\n Sew together with cast to recreate string<\/span><\/td>\n Ideal storage size, sorting, performance<\/span><\/td>\n<\/tr>\n
four binary(1)s<\/span><\/td>\n 4 b<\/span><\/td>\n Parse and two CASTs<\/span><\/td>\n View or computed column<\/span><\/td>\n Requires two casts to sort and read, code is harder to read\/maintain<\/span><\/td>\n Ideal storage size, obscure values<\/span><\/td>\n Obfuscate the data<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u00a0<\/strong><\/span><\/p>\n
Thanks for reading this article,<\/strong><\/span><\/p>\n
Next steps :<\/strong><\/span><\/p>\n
\n
Add this script to your database toolkit<\/strong><\/b><\/span><\/li>\n
Share this with your colleagues because Sharing is Learning<\/strong><\/b><\/span><\/li>\n
Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Scenario proposal 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? Solution This tutorial will work […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[53,8,52,13,12,51],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":4,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":248,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158\/revisions\/248"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

Comparison Table<\/strong><\/span><\/td>\n<\/tr>\n<\/thead>\n
Datatype<\/strong><\/span><\/td>\n	Storage<\/strong><\/span><\/td>\n	Writing<\/strong><\/span><\/td>\n	Displaying<\/strong><\/span><\/td>\n	Sorting<\/strong><\/span><\/td>\n	Drawback(s)<\/strong><\/span><\/td>\n	Benefit(s)<\/strong><\/span><\/td>\n<\/tr>\n
varchar(15)<\/span><\/td>\n	15 b<\/span><\/td>\n	Verbatim<\/span><\/td>\n	Verbatim<\/span><\/td>\n	Parse needed<\/span><\/td>\n	Highest disk space needed<\/span><\/td>\n	Readability, ease to insert<\/span><\/td>\n<\/tr>\n
four tinyints<\/span><\/td>\n	4 b<\/span><\/td>\n	Parse and one CAST<\/span><\/td>\n	View or computed column<\/span><\/td>\n	Native<\/span><\/td>\n	Sew together with cast to recreate string<\/span><\/td>\n	Ideal storage size, sorting, performance<\/span><\/td>\n<\/tr>\n
four binary(1)s<\/span><\/td>\n	4 b<\/span><\/td>\n	Parse and two CASTs<\/span><\/td>\n	View or computed column<\/span><\/td>\n	Requires two casts to sort and read, code is harder to read\/maintain<\/span><\/td>\n	Ideal storage size, obscure values<\/span><\/td>\n	Obfuscate the data<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u00a0<\/strong><\/span><\/p>\n Thanks for reading this article,<\/strong><\/span><\/p>\n Next steps :<\/strong><\/span><\/p>\n \n Add this script to your database toolkit<\/strong><\/b><\/span><\/li>\n Share this with your colleagues because Sharing is Learning<\/strong><\/b><\/span><\/li>\n Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Scenario proposal 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? Solution This tutorial will work […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[53,8,52,13,12,51],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":4,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":248,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/158\/revisions\/248"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}