Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2009-01-08 : 06:00:50
|
| Hi,Just wondering how many of you guys recommend storing IP addresses as INT's ? and how many of you guys actually do it ? I wasn't aware of this technique but was researching best practices and came across " Store IPs as integers with INET_ATON and retrieve them with INET_NTOA."I have some big logging tables, and perhaps this would be beneficial.Just looking for general feedback!Thanks again!Mike123 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 06:14:48
|
It depends what you are going to to with the adresses later.For space issues, an INT is only 4 bytes.192.168.0.1 is 192 * 16777216 + 168 * 65536 + 0 * 256 + 1 = 1084751873 (with 2nd compliment calculation).
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-01-08 : 06:23:34
|
| Hey Peso,ok gotcha .. lets say for a log table, 10's of millions of rows... would you stores as an INT ? If there was an index on the column would you store as an INT ? Thanks!mike123 |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-01-08 : 06:36:00
|
| also, are there functions comparable to INET_ATON and INET_NTOA in mySql for SQL Server ?CREATE FUNCTION [dbo].[inet_aton] (@IP VARCHAR(15)) RETURNS BIGINT AS BEGIN DECLARE @A BIGINT, @B BIGINT, @C BIGINT, @D BIGINT DECLARE @iBegin INT, @iEnd INT SELECT @iBegin=1 SELECT @iEnd=CHARINDEX('.', @IP) SELECT @A=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT) SELECT @iBegin=@iEnd+1 SELECT @iEnd=CHARINDEX('.', @IP, @iBegin) SELECT @B=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT) SELECT @iBegin=@iEnd+1 SELECT @iEnd=CHARINDEX('.', @IP, @iBegin) SELECT @C=CAST(SUBSTRING(@IP, @iBegin, @iEnd-@iBegin) AS BIGINT) SELECT @iBegin=@iEnd+1 SELECT @iEnd=CHARINDEX('.', @IP, @iBegin) SELECT @D=CAST(SUBSTRING(@IP, @iBegin, 15) AS BIGINT) DECLARE @IPNumber BIGINT SELECT @IPNumber=@A*16777216+@B*65536+@C*256+@D RETURN @IPNumber END |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 07:24:09
|
This is an easier calculationSELECT CAST(16777216.0 * PARSENAME(@IP, 4) + 65536 * PARSENAME(@IP, 3) + 256 * PARSENAME(@IP, 2) + 1 * PARSENAME(@IP, 1) AS BIGINT)It all depends on what you are going to use IP number to. If only for archive duties, store as INT because of lesser space taken.For display purposes? I'd make a [persisted] calculated column over IP [int] number.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2009-01-08 : 07:31:53
|
| Great suggestion... It is mainly for storing purposes, but also for display to a lesser extent. A calculated column does that take space? I can always use a function in the web application as well to convert, this way theres a tiny bit less IO to the sqlserver, which even tho tiny might help. Trying to optimize everything on this insert as its going to be run alot and performance really depends on it.Would you see passing INT's and retrieving INT's for IP addresses and converting them on the web application , as a beneficial approach?Thanks again!mike123 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 07:34:59
|
Calculated columns has almost no overhead in table. Only definition of the calculated column is stored.But if you use PERSISTED, the calculated data is stored in the table. And you can also index the calculated column.The value can't be edited directly, and is recalcalculated automatically when base columns are changed.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
storing IP addresses