en-UShe-IL
You are here:  Blog

Awared MVP 

Microsoft® Community Contributor 


Microsoft® Community Contributor


Blog Archive:

הגדל
* Can be used in order to search for older blogs Entries

Search in blogs


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


הגדל מה בעמוד?

יול29

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

Introduction & The Problem

Every machine on a network has a unique number assigned to it, called an IP address. There cannot be more than one machine on the network with the same IP. You can look at your IP address as if it were an ID. There are two types of IP Addresses that can be used on a network. The first, and the version that the Internet and most routers are currently configured for, is IPv4 or Internet Protocol version 4. This version uses 32-bit addresses. With this in mind, a new version of IP addresses was developed called IPv6, which would change the address size from 32-bit address to 128-bit addresses. In this article we shell talk on IPv4.

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

Hexadecimal number
http://en.wikipedia.org/wiki/Hexadecimal


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

Base convertor
http://wims.unice.fr/wims/wims.cgi

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
Location: Blogs Parent Separator Public blog