You are here:

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

 KeywordsPhrase

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords

TNWikiSummit

 Read this before you use the blog!

Storing IPv4 addresses in the Database

יול29

Written by: ronen ariely
29/07/2013 23:31

Introduction & The Problem

An IP address always consists of 4 numbers separated by periods. Each number have a possible value of 0 through 255. IP address appears format like: xxx.xxx.xxx.xxx

we can think on IP as four digits number on base 256. If you get the last sentence so you do not have to keep reading :-). This is the idea behind an IPv4 address and how to use it.

The Solution & Explanation

In order to store an IP address in this format we have to use a String type, but this is a big mistake.

Usually we use numbers on base 10. This is only an historical reason as we have 10 fingers people start calculate math using base 10. Actually this is not a natural base. In mathematics and computer science we use a binary numeral system, or base-2 numeral system, represents numeric values using two symbols: typically 0 and 1. This is a base on the way electricity device work sing on/off. As computers become more complex we moved to use a more complex base like base 16, also named hexadecimal. It uses sixteen distinct symbols, most often the symbols 0–9 to represent values zero to nine, and A, B, C, D, E, F.

The biggest four digit number on base 2 will look like 1111 (it's value on base 10 is just 2^3 + 2^2 + 2^1 +2^0 = 8+4+2+1 = 15), the biggest four digits number on base 10 is 9999, and the biggest four digits on base 16 is ffff (equal 65535 on base 10).

There are different bases for different use, but we can notice that as the base is bigger we can get a more complex number using less digest. Why not working on base 256? And IP address is actually a base 256 number.We can look at hexadecimal number with 4 digits as a 4 numbers which each number have a possible value of 0 through 16. The hexadecimal number ffff can be written as 16.16.16.16

We can look at IP address as a 4 digits number on base 256. As we don’t have 256 distinct symbols we write an IP number in the format xxx.xxx.xxx.xxx using numbers from 000 to 256.

Now that we understand the idea of base 256 it is very easy to understand the best way of storing IP address in the database. We will not use a STRING nut a numeric column, as IP is not a STRING but a number base 256. But as SQL server do not have a column type numeric base 256 we will use a BIGINT column. All that we need to do is to convert the base 256 number into base 10 number and store this number in the database. Now that we use simple numeric value we can index this column and get a fast and optimal work with IP addresses.

The Code

We will use this function for the convert from IP string to base 10 number:

Important!!! IP sometimes not written in three digits eg instead of 001 we use 1. In this case the current function will not work and you should use SPLIT function. In case if you want optimize result then you should use CLR function which gives the a lot of advantage in this case and use less resources. The basic idea is simply to split by a dot and then get a set of four Octet.

`CREATE` `FUNCTION` `ArielyIP2Number(@KInString ``as` `char``(15))`
`RETURNS` `BIGINT`
`WITH` `EXECUTE` `AS` `CALLER`
`AS`
`-- place the body of the function here`
`BEGIN`
`    ``declare` `@FirstOctet ``bigint` `= ``convert``(``int``, ``SUBSTRING``(@KInString,1,3))`
`    ``declare` `@SecondOctet ``int` `= ``convert``(``int``, ``SUBSTRING``(@KInString,5,3))`
`    ``declare` `@ThirdOctet ``int` `= ``convert``(``int``, ``SUBSTRING``(@KInString,9,3))`
`    ``declare` `@FourthOctet ``int` `= ``convert``(``int``, ``SUBSTRING``(@KInString,13,3))`
`    ``-- First Octet * (256*256*256); 16777216`
`    ``-- Second Octet * (256*256); 65536`
`    ``-- Third Octet * (256); 256`
`    ``-- Fourth Octet`

`    ``RETURN` `(@FirstOctet * 16777216) + (@SecondOctet * 65536) + (@ThirdOctet * 256) + (@FourthOctet);`
`END`
`GO`

And we can use this function to convert base 10 number back to a string IP number:

* CLR will NOT give you a mach better results here probably, stay with TSQL.

`CREATE` `FUNCTION` `ArielyNumber2IP(@KInNumber ``as` `bigint``)`
`RETURNS` `char``(15)`
`WITH` `EXECUTE` `AS` `CALLER`
`AS`
`-- place the body of the function here`
`BEGIN`
`    ``declare` `@FirstOctet ``bigint` `= @KInNumber / 16777216`
`    ``declare` `@SecondOctet ``int` `= (@KInNumber - (@FirstOctet * 16777216)) / 65536`
`    ``declare` `@ThirdOctet ``int` `= (@KInNumber - (@FirstOctet * 16777216) - (@SecondOctet * 65536)) / 256`
`    ``declare` `@FourthOctet ``int` `= (@KInNumber - (@FirstOctet * 16777216) - (@SecondOctet * 65536) - (@ThirdOctet * 256))`
`    ``-- First Octet * (256*256*256); 16777216`
`    ``-- Second Octet * (256*256); 65536`
`    ``-- Third Octet * (256); 256`
`    ``-- Fourth Octet`

`    ``RETURN`
`        ``convert``(``char``(3), @FirstOctet) `
`        ``+ ``'.'` `+ ``convert``(``char``(3), @SecondOctet) `
`        ``+ ``'.'` `+``convert``(``char``(3), @ThirdOctet) `
`        ``+ ``'.'` `+``convert``(``char``(3), @FourthOctet)`
`END`
`GO`

Examples

DDL+DML:

`create` `table` `ArielyIP_Tbl ([IP] ``BIGINT``, [``Name``] ``VARCHAR``(100))`
`GO`

`CREATE` `INDEX` `ArielyIP_Index`
`ON` `dbo.ArielyIP_Tbl`
`(`
`    ``[IP]`
`)`
`GO`

`insert` `ArielyIP_Tbl `
`values`
`    ``(dbo.ArielyIP2Number(``'255.254.222.001'``), ``'a'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.222.001'``), ``'b'``)`
`    ``, (dbo.ArielyIP2Number(``'255.255.222.002'``), ``'c'``)`
`    ``, (dbo.ArielyIP2Number(``'255.255.222.003'``), ``'d'``)`
`    ``, (dbo.ArielyIP2Number(``'255.255.222.004'``), ``'e'``)`
`    ``, (dbo.ArielyIP2Number(``'255.255.222.005'``), ``'f'``)`
`    ``, (dbo.ArielyIP2Number(``'255.255.222.006'``), ``'g'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.222.007'``), ``'h'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.333.008'``), ``'i'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.333.009'``), ``'j'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.333.001'``), ``'k'``)`
`    ``, (dbo.ArielyIP2Number(``'255.254.333.002'``), ``'l'``)`
`GO`

Example 01:  select all with order by IP

`select` `* ``from` `ArielyIP_Tbl`
`order` `by` `IP`
`GO`
`/*`
`4294893057  a`
`4294893057  b`
`4294893063  h`
`4294921473  k`
`4294921474  l`
`4294921480  i`
`4294921481  j`
`4294958594  c`
`4294958595  d`
`4294958596  e`
`4294958597  f`
`4294958598  g`
`*/`

Example 02: Looking an IP between 255.254.333.000 to 255.254.333.255

`declare` `@``Min` `BIGINT` `= dbo.ArielyIP2Number(``'255.254.333.000'``)`
`declare` `@``Max` `BIGINT` `= dbo.ArielyIP2Number(``'255.254.333.255'``)`
`select` `* ``from` `ArielyIP_Tbl`
`where` `IP ``between` `@``Min` `and` `@``Max`
`GO`
`/*`
`4294921480  i`
`4294921481  j`
`4294921473  k`
`4294921474  l`
`*/`

Resources

Binary number
http://en.wikipedia.org/wiki/Binary_number

Base convertor

Appendix A: other way to do it wrong

This publication came as almost always followed a discussion or a question asked. I would like to mention here one of the links posted in the discussion which presents some ideas for implementing this problem.

I want to stress that I do not show this because I want someone to use these solutions. I think this is very bad solutions. I present them just to keep you from falling in the same places others have thought.

All the option mention there are very bad and i do not recommended any of them

* The first option is using a varchar(15) - eazy to use, but very low optimization.

* The second option is using four tinyints - difficult to use, low optimization (better then first). Database should be a tool for the user and not just a stand along application. this is A pure DBA solution, probably not something that was written by someone that think about the developer side or any OOP model or any ORM use (like EF).

* The third option is using four binary(1)s - look at my comment for second. the same four column idea, and for what?!?

remark: By "using difficulty", i am talking about the users like a developer that need to use this for the application.

Tags: IP , CONVERT , base
Categories: SQL