SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storing IPs in SQL Server


Storing IPs in SQL Server

Author
Message
David.Poole
David.Poole
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7961 Visits: 3291
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/2871.asp

LinkedIn Profile

Newbie on www.simple-talk.com
Adam Selene-398546
Adam Selene-398546
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
You missed one obvious way to store IP addresses, which is both the smallest data size and yet still easy to interpret as an IP address -- binary(4).

127.0.0.1 becomes 0x7F000001

I wrote the following functions, and hereby declare them into the public domain:


create function dbo.ConvertIPAddressToBinary (@IPAddress varchar(15))
returns binary(4)
as
begin
declare @Octet1 binary(1), @Octet2 binary(1), @Octet3 binary(1), @Octet4 binary(1);

select @Octet1 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));
select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));
select @Octet2 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));
select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));
select @Octet3 = cast(cast(substring(@IPAddress, 1, charindex('.', @IPAddress) - 1) as tinyint) as binary(1));
select @IPAddress = substring(@IPAddress, charindex('.', @IPAddress) + 1, len(@IPAddress));
select @Octet4 = cast(cast(@IPAddress as tinyint) as binary(1));

return @Octet1 + @Octet2 + @Octet3 + @Octet4;
end
go

create function dbo.ConvertBinaryToIPAddress (@IPAddress binary(4))
returns varchar(15)
as
begin
declare @BigIP bigint;
declare @Octet1 tinyint, @Octet2 tinyint, @Octet3 tinyint, @Octet4 tinyint;

select @BigIP = cast(@IPAddress as bigint);

select @Octet4 = @BigIP % 256;
select @BigIP = @BigIP / 256;
select @Octet3 = @BigIP % 256;
select @BigIP = @BigIP / 256;
select @Octet2 = @BigIP % 256;
select @BigIP = @BigIP / 256;
select @Octet1 = @BigIP % 256;

return cast(@Octet1 as varchar) + '.' + cast(@Octet2 as varchar) + '.' + cast(@Octet3 as varchar) + '.' + cast(@Octet4 as varchar);
end
go
zootie
zootie
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 48
Just a side comment. Depending on the purpose and expected lifetime of the application, you might have to deal with IPv6 addresses (http://en.wikipedia.org/wiki/Ipv6). In that case, you'd have to consider larger data types. If you wanted to stick to built-in types, uniqueidentifier could be an option (not very human readable, but atomic)-or you could go for binary(16) (or maybe varbinary(16) thinking also of ipv4) as implied by earlier post.



RichB
RichB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 1058
Surely your choice of index for the large range could have been better? Yes the highly specific reversed order may be better for a particular lookup, but for a ranged lookup over all 4 levels surely an index in order would be better? After all, in this example you will need to go through ALL of the top 3 levels before you acquire any level of usable index? Seems pretty inefficient to me.



Andy Warren
Andy Warren
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Moderators
Points: 12119 Visits: 2730
Interesting. I'd be curious to see the query plan on the bigint implementation, and a link to the UDT implementation if anyone has one (though I agree the overhead in storage is substantial).

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1729 Visits: 2384

Good work, David.

I agree with the comments about IPv6 though. Easily expanded to 6. Our need is to validate IP's and to count the number of distinct rows over time for licensing. Some of these techniques could be quite helpful speeding performance.



ATBCharles Kincaid
noeld
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10034 Visits: 2048
I have seen this approach before and it *is* quite fast but when you try to make the returned values into readable format is when you get hit *hard* by performance.


* Noel
Adrian Hains
Adrian Hains
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 261
I'm not sure I see the need to have the data be human readable. I guess it depends on the specifics of your use case.

My assumption is that the index lookup would be best with int or binary(4) rather than having the index over 4 columns. Each comparison would be very direct this way. Not sure if this small perf gain is worth much though, depends on the circumstances. I have about 300MM rows of web logs (recent data only, I keep a rolling window of data) in the DB at the moment, so it might add up.. but I very rarely need to analyze by IP. When I do, it is always after already filtering based on a time window.

I would think that a good solution might be to just use int / binary(4) for indexing your IP and have a non-persisted/indexed computed column that has your human readable value. You would want simple mapping functions with most of these solutions, so just use your mapping function in the computed column for human readable. The major downside clearly is if you routinely want to render a large dataset of IPs rather than just filter or aggregate data on them.

I just use varchar(15) at the moment because it makes it dead simple to BCP in the IIS logs. I care very little about disk space because this is data I exclude from my backup regimen.

It's always good to know how you actually use your data for choosing the best strategy.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7961 Visits: 3291
Joe,

How do you feel about the naming of the integer types?

When I started programming integer meant 16 bit, long integer meant 32 bit. Not everything has shifted up a gear (no pun intended).

I notice that the recommendations for .NET development say that the developer should used the explicit types such as int32 and int64.

Calling 64 bit integers BIGINT strikes me as the equivalent of having giant squid, collosal squid and whatever name they'll think of for the next size squid.

Does this mean that when we move up to 128 bit computing we are going to see HUGEINT, ENORMOUSINT etc?

LinkedIn Profile

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search