InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:01:54 GMT20http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxNice article.Tue, 24 Jul 2012 05:20:17 GMTBasit Farooqhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxPARSENAME is a rEaLLy great tool here. Here's another way that isn't nearly as esthetically pleasing but is probably faster, just because it doesn't parse the string four times. In CLR form it would cook...[code="sql"]create function dbo.ConvertIp2NumC(@ipAddr nvarchar(15))returns bigintasbegin declare @ipPos int, @ipLen int, @ipChar nchar(1), @segNum int, @ipNum bigint set @segNum = 0 set @ipPos = 1 set @ipNum = 0 set @ipLen = len(isnull(@ipAddr, '')) while @ipPos <= @ipLen begin set @ipChar = substring(@ipAddr, @ipPos, 1) if @ipChar = '.' begin set @ipNum = (256 * @ipNum) + cast(@segNum as bigint) set @segNum = 0 end else set @segNum = (10 * @segNum) + cast(@ipChar as int) set @ipPos = @ipPos + 1 end set @ipNum = (256 * @ipNum) + cast(@segNum as bigint) return @ipNumend[/code]Fri, 20 Jul 2012 14:04:36 GMTron.hanehttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Jeff Moden (4/18/2010)[/b][hr] I'm asking because I'd [i]really [/i]like to quit.And, you're correct. I quit twice for a year each and once for 3 months and not sure why I went back to it other than I like it and just wanted "one". The old saying holds true... "You're a puff away from a pack a day."[/quote]It took me about a dozen tries to quit. Here is what worked for me:First and foremost, I cut out caffeine. Think about it, a nicotine fit looks just like being over-caffeinated.Second, after the taper down phase, on my quit date, I intentionally changed my self image to a non-smoker. Post signs on the bathroom mirror, the refrigerator, in your car, in your cubicle, etc. As you go to sleep at night create a mental image of yourself as a non-smoker. Maybe on a tropical beach, maybe wearing white at a party with beautiful women all wearing white, whatever works for you.Then when I felt a craving for a cigarette I would laugh at myself "don't be silly, you are a non-smoker." Nicotine is like the alien being in “Day of the Dove” http://en.wikipedia.org/wiki/Day_of_the_Dove feeding on your negative emotions. Laughing at it drives it away, stressing over it feeds it.Good luck. As others have said you are a very special person and a major resource to many of us. It would be a shame to lose you before your time.===============================Anyway, back to the topic, I am trying to remember if anyone tested PARSENAME vs using a Tally Table. In this instance the tally table would only need to be 15 rows.Fri, 20 Jul 2012 08:14:34 GMTsteven.malonehttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[code]DECLARE @IPvalue BIGINT = 3222211197, @IPstring VARCHAR(15) = '192.15.10.125'-- SwePeso (value to string)SELECT CAST(CAST(SUBSTRING(Data, 1, 1) AS TINYINT) AS VARCHAR(3)) + '.' + CAST(CAST(SUBSTRING(Data, 2, 1) AS TINYINT) AS VARCHAR(3)) + '.' + CAST(CAST(SUBSTRING(Data, 3, 1) AS TINYINT) AS VARCHAR(3)) + '.' + CAST(CAST(SUBSTRING(Data, 4, 1) AS TINYINT) AS VARCHAR(3)) AS IPFROM ( VALUES (CAST(@IPvalue AS BINARY(4))) ) AS d(Data)-- SwePeso (string to value)SELECT CAST(16777216E * PARSENAME(@IPstring, 4) + 65536E * PARSENAME(@IPstring, 3) + 256E * PARSENAME(@IPstring, 2) + 1E * PARSENAME(@IPstring, 1) AS BIGINT) AS IP[/code]Fri, 20 Jul 2012 07:09:43 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]ozkary (7/10/2009)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server/67215/">Map IP Address to a Geographical Location</A>[/B][/quote]hi friendyou can get the location details of a particular ip address using this sitewww.ip-details.comthey provide information about a particular ip address.Mon, 28 Feb 2011 04:11:25 GMTumaipde1http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxGeocity light is intentionally left wanting. Purchase the DB for more accurate results!Mon, 08 Nov 2010 16:41:42 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxLink to format files for the maxmind geocity csvhttp://www.dyndnsservices.com/Format-files.zipMon, 08 Nov 2010 16:34:52 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxhttp://www.dyndnsservices.com/knowshow.aspx?ID=4 has both the bulk insert procedure and needed format files for the geocity csv, per gratis.Thank you maxmind!!!Mon, 08 Nov 2010 16:33:11 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxI am not getting the data for the ip address 180.215.5.170 it's @ipnum coming to 3033990570. But when i checked it over the net it shows me City:Bangalore Region:Karnataka Country:India, but in the database no data for that.And same happened with the ip address that you have used in your article that is 192.15.10.125 you have show a entry for that but with i am not getting it, i have used this query :-declare @ip varchar(50), @ipnum bigintset @ip = '192.15.10.125'select @ipnum = dbo.ConvertIp2Num(@ip)print @ipnumselect loc.locId,loc.country,loc.region,loc.city,loc.postalCode,loc.latitude,loc.longitude,loc.areaCodefrom [GeoLiteCity_blocks] blk (nolock)inner join [GeoLiteCity_location] loc (nolock)on blk.locId = loc.locIdwhere @ipnum between blk.startIpNum and blk.endIpNumis this query right?i ahve used the latest csv files.please help i am really wanting to extract something new form this, but not getting the basic. Mon, 08 Nov 2010 16:28:02 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]richardn-1128243 (4/21/2010)[/b][hr]This is great - despite the limitations on accuracy.I have a collection of IP Addresses in the Visitor_Log table, which I convert to an IPNum - no problems there - then I wish to do a join to the Blocks table and thus the Location table. If I do very small subset (say 13 IPNums) and do a INNER JOIN with the BETWEEN criteria it takes about 13 seconds. When I do it with 300 IPNums it takes over 10 mins. Looks like the BETWEEN is too heavy.Any ideas how I could make this thing run like "a cut cat"?Here's the query being used:-[code="sql"]SELECT l.Referrer, l.IPAddress, l.IPNum, b.locId, cl.country, cl.region, cl.city, cl.postalCode, cl.latitude, cl.longitude, cl.areaCode, cl.metroCode, b.startIpNum, b.endIpNumFROM Visitor_Log AS l INNER JOIN GeoLiteCity_Blocks AS b ON l.IPNum [b][u]BETWEEN[/u][/b] b.startIpNum AND b.endIpNum INNER JOIN GeoLiteCity_Location AS cl ON b.locId = cl.locId[/code][/quote]Use the "trick" given in [url=http://www.sqlservercentral.com/Forums/FindPost830703.aspx]post 830703[/url]. I've definitely noticed an improvement in my queries from the BETWEEN operator. Also, I put a 100% fillfactor clustered index on StartIP, EndIP and not separate indexes on each. Even though it takes additional space, to reduce the time for the query to execute even further, I made an indexed persisted computed column on the log tables (where the IP is stored) and use the computed column (LongIP) in the queries instead of computing it on the fly.Fri, 23 Apr 2010 07:35:21 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxThis is great - despite the limitations on accuracy.I have a collection of IP Addresses in the Visitor_Log table, which I convert to an IPNum - no problems there - then I wish to do a join to the Blocks table and thus the Location table. If I do very small subset (say 13 IPNums) and do a INNER JOIN with the BETWEEN criteria it takes about 13 seconds. When I do it with 300 IPNums it takes over 10 mins. Looks like the BETWEEN is too heavy.Any ideas how I could make this thing run like "a cut cat"?Here's the query being used:-[code="sql"]SELECT l.Referrer, l.IPAddress, l.IPNum, b.locId, cl.country, cl.region, cl.city, cl.postalCode, cl.latitude, cl.longitude, cl.areaCode, cl.metroCode, b.startIpNum, b.endIpNumFROM Visitor_Log AS l INNER JOIN GeoLiteCity_Blocks AS b ON l.IPNum [b][u]BETWEEN[/u][/b] b.startIpNum AND b.endIpNum INNER JOIN GeoLiteCity_Location AS cl ON b.locId = cl.locId[/code]Wed, 21 Apr 2010 17:56:37 GMTrichardn-1128243http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Lowell (4/16/2010)[/b][hr]i must have tunnel vision this morning;i didn't see any BULK insert scripts for the files; after downloading the csv files, i see they are UNIX style(slash r for the row delimiter, right?), but they are also dbl-quote delimited, so i need a format file.did anyone already do this, or do i need to get some coffee and sweat it out?[/quote]I built a quick SSIS 2008 package to do this, it truncs the table and then repopulates it from the files.[url]http://downloads.novaconceptsltd.com/SSC/GeoLiteLoad.zip[/url]Put files in C:\TempPretty straightforward package..CEWIISun, 18 Apr 2010 22:48:11 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]sam-610757 (4/16/2010)[/b][hr]I smoked for 20+ years. I have not had a cigarette in 2.5 years give or take. This is what it took for me to quit. I chewed the gum and suckwed on the lozenges for 8 months, 4MG one after another. I switched to 2MG about a month before I stopped completely. After many months on the gum you will notice that you will wake up and not even think to get a piece sometimes, and after the habit of having a smoke has been broken choose a day like this to quit.Sneaking even one cigarette is to start all over, try to avoid people who smoke while your quitting. I can be around smokers now without issue. I thought I loved cigarettes as well, but I wouldn’t go back for anything. [/quote]Really interesting. I know what "the box" says but did you smoke while you chewed the gum and sucked on the lozenges or did you stop smoking as soon as started the substitutes? I'm asking because I'd [i]really [/i]like to quit.And, you're correct. I quit twice for a year each and once for 3 months and not sure why I went back to it other than I like it and just wanted "one". The old saying holds true... "You're a puff away from a pack a day."Sun, 18 Apr 2010 15:41:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]stevefromOZ (4/18/2010)[/b][hr]Off topic - Hey Jeff, head to Australia where they're contemplating $20 (!!!) a packet, am sure this could influence your behaviour ;)[/quote]Heh... it sure would. I'd build a quarter acre greenhouse and grow my own as an "insecticide". ;-)Sun, 18 Apr 2010 15:30:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxWhilst the article was very good, I would like to issue a note of caution; even if the address does not appear to be that of a DHCP pool used by an ISP, the results may still be misleading as:1) The traffic is routed via the company WAN to a quite different address - in an extreme case I have seen this give my address as being the United States whilst I was in England.2) The address is that of the failities management company rather then the actual address. Again I have seen this give an address in Germany whereas I was definitly in the UK.Sun, 18 Apr 2010 15:10:11 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxOff topic - Hey Jeff, head to Australia where they're contemplating $20 (!!!) a packet, am sure this could influence your behaviour ;)Sun, 18 Apr 2010 11:10:35 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Jeff Moden (4/16/2010)[/b][hr][quote][b]webrunner (4/16/2010)[/b][hr][quote][b]Jeff Moden (7/25/2009)[/b][hr]Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. :-D I'm a bit touchy because I'm trying to quit smoking, again....[/quote]Hi Jeff,I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!- webrunner[/quote]Heh... thanks. My biggest problem is that I really, really like smoking.[/quote]I smoked for 20+ years. I have not had a cigarette in 2.5 years give or take. This is what it took for me to quit. I chewed the gum and suckwed on the lozenges for 8 months, 4MG one after another. I switched to 2MG about a month before I stopped completely. After many months on the gum you will notice that you will wake up and not even think to get a piece sometimes, and after the habit of having a smoke has been broken choose a day like this to quit.Sneaking even one cigarette is to start all over, try to avoid people who smoke while your quitting. I can be around smokers now without issue. I thought I loved cigarettes as well, but I wouldn’t go back for anything. Fri, 16 Apr 2010 15:07:35 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxI enjoyed revisiting this article. This is a job well done.Fri, 16 Apr 2010 13:20:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxGreat timing and write up. I started down this path with SQL 2K8 and GeoCity data. I'm an ESRI user and plan to mix this with spatial data stored in SQL spatial and ESRI's ArcSDE. This will provide a good sampling of our map application's usage and reach in....a map application--how novel.Instead of using a bulk insert for user logs wouldn't IIS logging in SQL work well here?Fri, 16 Apr 2010 13:06:04 GMTskocsishttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]webrunner (4/16/2010)[/b][hr][quote][b]Jeff Moden (7/25/2009)[/b][hr]Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. :-D I'm a bit touchy because I'm trying to quit smoking, again....[/quote]Hi Jeff,I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!- webrunner[/quote]Heh... thanks. My biggest problem is that I really, really like smoking.Fri, 16 Apr 2010 12:51:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Jeff Moden (7/25/2009)[/b][hr]Heh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. :-D I'm a bit touchy because I'm trying to quit smoking, again....[/quote]Hi Jeff,I'm rooting for you. I don't smoke, but I've seen how hard it is for people to quit. It can be done, though. And given what I have seen of the quality of your code and tenacity in solving SQL problems, you sure seem to have the stick–to–itiveness to achieve your goal!- webrunnerFri, 16 Apr 2010 09:26:19 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxNice summary. We bought IP to city range a few years ago for $100 and it's served us nicely since, allowing us to show (albeit not ENTIRELY accurate) our customers numbers of page views within a distance range from their location. Also it's nice to have it mapped to zipcode.Fri, 16 Apr 2010 09:10:57 GMTbez7-793740http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxwill come in handy. thanksFri, 16 Apr 2010 08:29:54 GMThadrianhallhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Mark-101232 (7/24/2009)[/b][hr][quote][b]Jeff Moden (7/24/2009)[/b][hr][quote][b]Mark (7/13/2009)[/b][hr]I think both functions can be simplified[code]create function dbo.ConvertIp2Num(@ip nvarchar(15))returns bigintasbegin return ((cast(parsename(@ip,4) as bigint)*256+ cast(parsename(@ip,3) as bigint))*256+ cast(parsename(@ip,2) as bigint))*256+ cast(parsename(@ip,1) as bigint)end[/code][/quote]That won't return the correct answer... the octets must be multiplied by powers of 256.[/quote]select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)[/quote]Surprisingly, the function above seemed to take longer on average than the function below (which is what was shown earlier):[code]CREATE FUNCTION [dbo].[fnDot2LongIP](@ipaddr varchar(15))RETURNS bigintASBEGIN DECLARE @longip bigint SELECT @longip = CAST(PARSENAME(@ipaddr, 1) AS bigint) % 256 + CAST(PARSENAME(@ipaddr, 2) AS bigint) % 256 * 256 + CAST(PARSENAME(@ipaddr, 3) AS bigint) % 256 * 65536 + CAST(PARSENAME(@ipaddr, 4) AS bigint) % 256 * 16777216 RETURN @longipEND[/code]As for the modulus operations, I tried the function with and without them. It seemed slightly quicker WITH them rather than without them. Does anyone else have the same results?Fri, 16 Apr 2010 08:19:14 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Lowell (4/16/2010)[/b][hr]i must have tunnel vision this morning;i didn't see any BULK insert scripts for the files; after downloading the csv files, i see they are UNIX style(slash r for the row delimiter, right?), but they are also dbl-quote delimited, so i need a format file.did anyone already do this, or do i need to get some coffee and sweat it out?[/quote][url]http://www.dyndnsservices.com/knowshow.aspx?ID=4[/url] has the bulk insert/import and the required format files etc. Also importing the data can be a little like beating a dead horse unless you restore correct ASCII formatting to the files. Just open and save them using wordpad before importing.Fri, 16 Apr 2010 07:36:03 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxi must have tunnel vision this morning;i didn't see any BULK insert scripts for the files; after downloading the csv files, i see they are UNIX style(slash r for the row delimiter, right?), but they are also dbl-quote delimited, so i need a format file.did anyone already do this, or do i need to get some coffee and sweat it out?Fri, 16 Apr 2010 07:05:18 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxVery good article.In practice, have you checked the accuracy against other geocoding techniques? I'm curious with users who do not posess static IP addresses whether IP resolves to the Telco location rather than the actual user.:Whistling:Does anyone have insight on this?Fri, 16 Apr 2010 06:38:17 GMTMartin Vriezehttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Jeff Moden (7/25/2009)[/b][hr]...I'm a bit touchy because I'm trying to quit smoking, again...[/quote]Good job I'm never giving up ;-)Fri, 16 Apr 2010 05:38:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxTwo things on the PARSENAME conversion routines:First, T-SQL scalar functions have no place in this world - the routines would be much more happily expressed in an in-line table-valued function.Second, PARSENAME is non-deterministic, despite what Books Online says. See [url]https://connect.microsoft.com/SQLServer/feedback/details/488058/parsename-incorrectly-documented-as-deterministic[/url]A deterministic iTVF IP-to-BIGINT function:[code="sql"]CREATE FUNCTION [dbo].[itfn_IPv4_Octets] (@IP NVARCHAR(15))RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT octet1 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, 1, (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END, octet2 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - (CHARINDEX(N'.', @IP, 1)) - 1)), N'') ELSE N'' END, octet3 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) - 1)), N'') ELSE N'' END, octet4 = CASE WHEN LEN(@IP) - LEN(REPLACE(@IP, N'.', N'')) = 3 THEN ISNULL(CONVERT(NVARCHAR(3), SUBSTRING(@IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)) + 1, LEN(@IP) - (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, (CHARINDEX(N'.', @IP, 1)) + 1)) + 1)))), N'') ELSE N'' END;[/code]That happens to split the IP address into octets, so converting further into a BINARY(4) - as suggested earlier - is not too hard:[code="sql"]SELECT CONVERT ( BINARY(4), CHAR(FN.octet1) + CHAR(FN.octet2) + CHAR(FN.octet3) + CHAR(FN.octet4) )FROM [dbo].[itfn_IPv4_Octets] ('162.74.5.51') FN;[/code]Conversion from BIGINT to dotted-octets:[code="sql"]CREATE FUNCTION dbo.IntToIP (@IP BIGINT)RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT ip_address = CONVERT(VARCHAR(3), ((@IP & 0xFF000000) / 256 / 256 / 256)) + '.' + CONVERT(VARCHAR(3), ((@IP & 0xFF0000) / 256 / 256)) + '.' + CONVERT(VARCHAR(3), ((@IP & 0xFF00) / 256)) + '.' + CONVERT(VARCHAR(3), (@IP & 0xFF));[/code]Notwithstanding some icky implicit conversions, that function will decode the BINARY(4) representation too.Now those functions might look a bit hairy, but being deterministic, they're fully foldable, so the estimated execution plans for the following statements:[code="sql"]SELECT * FROM dbo.itfn_IPv4_Octets(N'162.74.5.51');SELECT * FROM dbo.IntToIP(0xA24A0533);[/code]...are both Constant Scans, with the answers fully listed in the operator - all the maths is done at compilation time, not execution time...which I think is remarkable.Fri, 16 Apr 2010 04:28:49 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxhttp://www.dyndnsservices.com/knowshow.aspx?ID=4 has a complete solution for the maxmind geo city databaseThu, 15 Apr 2010 22:35:42 GMTsam-610757http://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxGreat article!I had downloaded the following scripts for a very similar database schema, that speeds up "select" queries against the blocks table. It increased my "lookups per second" from around 45 to 3500, really.I dropped indexes on GeoLiteCity_blocks table and created the following two indexes:[code="sql"]CREATE NONCLUSTERED INDEX [NCL_END_IP] ON [dbo].[GeoLiteCity_blocks] ( [endIpnum] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [NCL_IND_STARTIP] ON [dbo].[GeoLiteCity_blocks] ( [startIpnum] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO[/code]Then to find the LocationID or any other column for a specific IP number is as follows:[code="sql"]declare @IP bigintset @IP=123456789select * from GeoLiteCity_blockswhere startIpnum=(select max(startIpnum) from GeoLiteCity_blocks where startIpnum<=@IP)and endIpnum >=@IP[/code]Please check this on a development environment with backups and tell me the results. Any comparisons would be great actually, I never had the time to do that.By the way, I could not find the original source of this trick. If anyone knows, you may add it also.Tue, 08 Dec 2009 08:04:18 GMTadnan.korkmazhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxHeh... sorry Peter... Thought you were trying to flatten the trajectory of a pork chop for some reason. :-D I'm a bit touchy because I'm trying to quit smoking, again. Right now, I hate just about everything... even the damned TV. I was watching "Moonshot" to try to take my mind off of smokes and guess what the freakin' astronauts and their wives were doing? SMOKING!! :sick: I can't win. :-P Scotty, beam me the hell out of here! :pinch:Sat, 25 Jul 2009 21:27:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxOf course not!I just googled the keywords and the link popped up.Sat, 25 Jul 2009 13:13:31 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Peso (7/25/2009)[/b][hr]Or see this topic from 2006...[/quote]Refreshers are never a bad thing. ;-)Sat, 25 Jul 2009 12:36:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxAnd with some error checking[code]alter function dbo.ConvertIp2Num(@ip varchar(15))returns bigintasbegin return case when @ip like '%[^0-9.]%' then NULL when @ip like '%.%.%.%.%' then null when @ip like '%..%' then null when @ip like '%[0-9][0-9][0-9][0-9]%' then null else 16777216.0 * parsename(@ip, 4) + 65536 * parsename(@ip, 3) + 256 * parsename(@ip, 2) + 1 * parsename(@ip, 1) endend[/code]Or see this topic from 2006http://www.sqlservercentral.com/Forums/Topic302100-145-1.aspxSat, 25 Jul 2009 03:12:58 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxNo need to cast either[code]alter function dbo.ConvertIp2Num(@ip varchar(15))returns bigintasbegin return 16777216.0 * parsename(@ip, 4) + 65536 * parsename(@ip, 3) + 256 * parsename(@ip, 2) + 1 * parsename(@ip, 1)end[/code]Sat, 25 Jul 2009 02:49:57 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]ozkary (7/24/2009)[/b][hr]hi, yes, fine tuning the queries to get the best performance is always welcome.For production environment, I suggest to process/publish the conversion of IP to num during the import process. This way applications are not affected by the overhead.thanks for the feedback[/quote]Heh...yes... unless you have to do it on the fly during a session.Fri, 24 Jul 2009 14:07:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspxhi, yes, fine tuning the queries to get the best performance is always welcome.For production environment, I suggest to process/publish the conversion of IP to num during the import process. This way applications are not affected by the overhead.thanks for the feedbackFri, 24 Jul 2009 12:12:20 GMTozkaryhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx@ Mark...Heh... that's what I get for looking at stuff before the caffeine reaches the brain. Thanks, Mark... nicely done.Fri, 24 Jul 2009 10:05:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic751105-1516-1.aspx[quote][b]Jeff Moden (7/24/2009)[/b][hr][quote][b]Mark (7/13/2009)[/b][hr]I think both functions can be simplified[code]create function dbo.ConvertIp2Num(@ip nvarchar(15))returns bigintasbegin return ((cast(parsename(@ip,4) as bigint)*256+ cast(parsename(@ip,3) as bigint))*256+ cast(parsename(@ip,2) as bigint))*256+ cast(parsename(@ip,1) as bigint)end[/code][/quote]That won't return the correct answer... the octets must be multiplied by powers of 256.[/quote]select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)Fri, 24 Jul 2009 09:11:03 GMTMark-101232