Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Storing IPv4 Addresses for Performance Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 11, 2009 2:23 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 22, 2010 12:12 AM Points: 21, Visits: 81
Post #768952
 Posted Wednesday, August 19, 2009 4:03 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, August 16, 2010 6:05 AM Points: 4, Visits: 8
 Nice article. Very informative.But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.For example: 127.0.0.1 would result in -16777215.
Post #773328
 Posted Wednesday, August 19, 2009 5:21 AM
 Valued Member Group: General Forum Members Last Login: Thursday, February 27, 2014 6:45 AM Points: 53, Visits: 166
 1 question:What if we create the solution using CLR? Is it possible? And what about the performance, since it's compiled?Thanks!!!
Post #773369
 Posted Wednesday, August 19, 2009 6:16 AM
 Grasshopper Group: General Forum Members Last Login: Monday, December 16, 2013 4:17 AM Points: 24, Visits: 215
 I store these as four tinyint columns. Also uses just four bytes but is human-readable, does not require all your conversions, has implicit validation, and better meets the 1NF goal of atomicity.
Post #773409
 Posted Wednesday, August 19, 2009 6:35 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, March 03, 2014 9:40 AM Points: 40, Visits: 89
 nicholastjh (8/19/2009)Nice article. Very informative.But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.For example: 127.0.0.1 would result in -16777215.That's the whole point. Some IP addresses will map to negative integers. As there are 2^32 IP Addresses and 2^31 non-negative integers in SQL Server we have to use positive as well as negative values.The idea with four columns - while not bad - has some flaws:1. There is no merits with 1NF as IP address is a complete being. Would you store postal code with few char(1) columns? 2. If you want to perform table wide bit operation with mask (mask may be from 0 to 32 actually) you will have to either map four tinyints to one int the same way it is presented in the article or map it to bigint. Not good for performance :>If you want readability - just use view with some ip_int_to_ip_string computed column.
Post #773430
 Posted Wednesday, August 19, 2009 6:36 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, February 27, 2014 10:30 AM Points: 860, Visits: 235
 Darn it HansVE, you beat me to the punch! I was going to say exactly the same thing. I don't see why someone would go to all of the trouble to convert IPv4 values this way, and back again, when it's far easier just to store them as four tinyint values.
Post #773431
 Posted Wednesday, August 19, 2009 7:40 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, March 21, 2011 10:44 AM Points: 2, Visits: 10
 I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:`DECLARE @IpBin binary(4)DECLARE @IpString varchar(15)SET @IpString = '101.202.33.44'--convert string to binarySET @IpBin = CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 4))) + CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 3))) + CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 2))) + CONVERT(binary(1), CONVERT(tinyint, PARSENAME(@IpString, 1)))SELECT @IpBin--convert binary to stringSELECT CAST(CAST(SUBSTRING(@IpBin,1,1) AS tinyint) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(@IpBin,2,1) AS tinyint) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(@IpBin,3,1) AS tinyint) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(@IpBin,4,1) AS tinyint) AS varchar(3))`
Post #773495
 Posted Wednesday, August 19, 2009 7:49 AM
 Grasshopper Group: General Forum Members Last Login: Monday, December 16, 2013 4:17 AM Points: 24, Visits: 215
 nimdil (8/19/2009)The idea with four columns - while not bad - has some flaws:1. There is no merits with 1NF as IP address is a complete being. Would you store postal code with few char(1) columns? 2. If you want to perform table wide bit operation with mask (mask may be from 0 to 32 actually) you will have to either map four tinyints to one int the same way it is presented in the article or map it to bigint. Not good for performance :>If you want readability - just use view with some ip_int_to_ip_string computed column.1. You do not know of subnetting? Do you also store first and last names of people together in one column?2. "Table wide bit operation" can be done using more than one column. Masks also are typically written by octet(s).A "ip_int_to_ip_string computed column" will impede performance, as will the functions in the article.The only "flaw" is that predicates, joins, etc. may need to use more than one column, and so more typing. The flaws with using integer are obvious from the need for such an article as this.
Post #773508
 Posted Wednesday, August 19, 2009 8:24 AM
 SSCertifiable Group: General Forum Members Last Login: Friday, January 03, 2014 3:52 PM Points: 6,066, Visits: 5,277
Post #773565
 Posted Wednesday, August 19, 2009 8:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, March 06, 2014 8:56 AM Points: 121, Visits: 1,229
 tdcheek (8/19/2009)I've always liked storing IP's as binary(4) - still 4 bytes, but simpler conversions:I like this format as well, since that's essentially what IP addresses are anyway. Like using INT and four separate TINYINT columns, it is efficient on space and has implicit validation. However, it has a few other advantages I like:1) The queries I perform are very simple and efficient. Most of them are sargable. You don't have the problem of half the address space being negative that you have with INT. It is very easy to say:`WHERE @IPAddress BETWEEN RangeLowerLimit AND RangeUpperLimit`2) When viewed in a query window, it is represented as a hexadecimal sequence. That makes it just as easy to compare two addresses visually as the dotted notation. 3) It's relatively simple to convert to dotted notation if necessary.Andrew --Andrew
Post #773566

 Permissions