Datatype for IP addresses

Question

Datatype for IP addresses

Melissa.Fischer

SSC-Addicted

Points: 421
More actions
October 14, 2008 at 5:48 am

#383051

I'm looking for recommendations on what the best datatype would be that will store IP address information.
Sometimes we have a need to run reports for activity from IP addresses but want to exclude certain address ranges at times. Depending on what datatype you use the exclusion of address ranges can get tricky.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

The most flexible solution I have used is to store the IP address parts in individual fields and use a calculated field to concatenate it together. Then it becomes individual small integer fields.

D.Oc SSCrazy Points: 2875 More actions · Answer 2

maybe you should read this

http://sqlblog.com/blogs/denis_gobo/archive/2008/10/05/9266.aspx

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

davecason SSC-Addicted Points: 411 More actions · Answer 3

I think a 4 byte binary field is the best. You can use substring function to pick out the octets and it takes the least amount of work to convert back and forth.

You can use a view or a computed column to convert the binary data back into a varchar(15) ip address as text if you need it.

You first use parsename ,then convert to tinyint, then convert to binary(1), then concatenate.

Here is a basic example with no error handling:

CREATE FUNCTION [dbo].[ipTxtToBin]

(

@ip varchar(15)

)

RETURNS binary(4)

AS

BEGIN

DECLARE @o1 binary(1), @o2 binary(1), @o3 binary(1), @o4 binary(1)

SELECT

@o1 = CONVERT(binary(1),CONVERT(tinyint,PARSENAME(@ip,4))),

@o2 = CONVERT(binary(1),CONVERT(tinyint,PARSENAME(@ip,3))),

@o3 = CONVERT(binary(1),CONVERT(tinyint,PARSENAME(@ip,2))),

@o4 = CONVERT(binary(1),CONVERT(tinyint,PARSENAME(@ip,1)))

RETURN @o1+@o2+@o3+@o4

END

And to test it:

declare @ip VARCHAR(15)

set @ip = '255.128.64.32'

declare @bin binary(4)

select @bin = [dbo].[ipTxtToBin] (@ip)

select @ip as ip

, @bin as bin

, convert(tinyint,substring(@bin,1,1)) as octet1

, convert(tinyint,substring(@bin,2,1)) as octet2

, convert(tinyint,substring(@bin,3,1)) as octet3

, convert(tinyint,substring(@bin,4,1)) as octet4

Result:

255.128.64.32|0xFF804020|255|128|64|32

You save between 3 to 11 bytes per IP address. You can filter on ranges using numeric assessments or through bitwise operations.

Here is a link to describe bit masks and CIDR if you want to use those ideas to filter your result:

http://www.countryipblocks.net/networking/identifying-the-network-and-broadcast-address-of-a-subnet/

nigel. SSChampion Points: 11636 More actions · Answer 4

Do you need to consider IPv6 addresses as well as IPv4?

edit: Just see the date on the original post! :blush:

Nigel
How to post questions

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Melissa.Fischer (10/14/2008)
Depending on what datatype you use the exclusion of address ranges can get tricky.

:blink: why? anything is cast(able)

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

I split them to octets (using PARSENAME) stored as TINYINT and, like someone suggested, use a calculated column to reassemble them just to make life a little easier. The suggestion of binary storage is fine but the TINYINT method doesn't take any additional storage and humans seem to like it better. 😉 Subnet masking won't be a problem, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 7

I'm with Jeff. I like them in separate fields, and then assemble them in another field if needed. As numerics they sort well, and the separation to me makes it easy to read when you're looking at the data.

Ray Laubert SSCrazy Points: 2910 More actions · Answer 8

I used a char(15) data type with a rule for format.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

We had a BIG discussion on this recently.

http://www.sqlservercentral.com/articles/IPv4+Addresses/67534/

Be sure to review the discussion..

http://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx

CEWII

davecason SSC-Addicted Points: 411 More actions · Answer 10

I actually tested 4 tiny int conversions and 1 int conversions and binary always beat 'em in conversion costs (cheaper on the CPU and the same size). If you have trouble with readability, use a computed column for the octets or the full address. Here is a reverse function for binary... and notice how simple both functions are:

CREATE FUNCTION [dbo].[ipBinToTxt]

(

@ip binary(4)

)

RETURNS varchar(15)

AS

BEGIN

DECLARE @o1 varchar(3), @o2 varchar(3), @o3 varchar(3), @o4 varchar(3)

SELECT

@o1 = CONVERT(varchar(3),CONVERT(tinyint,SUBSTRING(@ip,1,1))),

@o2 = CONVERT(varchar(3),CONVERT(tinyint,SUBSTRING(@ip,2,1))),

@o3 = CONVERT(varchar(3),CONVERT(tinyint,SUBSTRING(@ip,3,1))),

@o4 = CONVERT(varchar(3),CONVERT(tinyint,SUBSTRING(@ip,4,1)))

RETURN @o1+'.'+@o2+'.'+@o3+'.'+@o4

END

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

I think the end result of that discussion was that there wasn't significant differences between the several methods. How you are going to use the data was the single greatest factor in your decision..

In other words choose your poison...

CEWII

Paul White SSC Guru Points: 150467 More actions · Answer 12

Jeff Moden (12/5/2009)
I split them to octets (using PARSENAME) stored as TINYINT and, like someone suggested, use a calculated column to reassemble them just to make life a little easier.

This has always been my approach too. The only slight irritation is that PARSENAME is non-deterministic (despite what Books Online says) so computed columns based on PARSENAME can't be persisted, and UDFs using it can't be schema-bound.

It is possible to replace the PARSENAME calls using CHARINDEX and SUBSTRING (which are both deterministic).

The next refresh of BOL will see PARSENAME moved from the deterministic to non-deterministic section.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

davecason SSC-Addicted Points: 411 More actions · Answer 13

You can use charindex with substring() but it is slower than parsename. I don't know why. I would love to see the code behind them to understand why.