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
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.