Datatype for IP addresses

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

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

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

  • 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:

    ip|bin|octet1|octet2|octet3|octet4

    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/

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

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

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

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

  • 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

  • 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

  • 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

  • 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

  • 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

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

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

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