4

What are the various ways to convert a varchar iPv4 Address into a number without using PARSENAME() (non-deterministic) function in SQL Server, in order to create a persisted calculated column?

I used the string manipulation technique from Extract part of string based on nth instance of character to create the following:

Query

select 
((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),28, 9)))))

Here is a visual of what the string manipulation looks like before each segment is converted to BIGINT and multiplied: IP String Manipulation

... This works to create a deterministic calculated column that can be persisted. What other more efficient techniques are there out there?

3
  • Can you provide an example of the output you are looking for?
    – datagod
    Oct 7, 2016 at 19:11
  • a BIGINT. so for '255.255.255.255' the output would be 4,294,967,295
    – Devon Ward
    Oct 8, 2016 at 20:44
  • 1
    You have asked for efficiency gains, so it might be useful to provide a test harness. Oct 8, 2016 at 22:59

4 Answers 4

7

One option is to use SQLCLR. It is rather easy to create a method to split the string on the periods, convert each to an Int64, and multiply by the appropriate power of 256. Be sure to set IsDeterministic=true in the SqlFunction attribute, which will not only allow it to be persisted, but will also allow it to participate in parallel plans :-). And in fact, the SQL# SQLCLR library (that I created) contains functions (which are in the Free version) to do this conversion: INET_AddressToNumber and INET_NumberToAddress.

Test Setup:

CREATE TABLE #TempAddresses (IPAddress VARCHAR(15) NULL);

-- populate 1 million rows, with a NULL every 1000 rows
;WITH cte AS
(
  SELECT  TOP (1000000) ac1.column_id,
          ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [RowNum]
  FROM    master.sys.all_columns ac1
  CROSS APPLY  master.sys.all_columns ac2
)
INSERT INTO #TempAddresses ([IPAddress])
SELECT CASE cte.[RowNum] % 1000
         WHEN 0 THEN NULL
         ELSE CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
       END
FROM   cte

Regarding the following three tests, please note:

  • They are shown in order of fastest to slowest.
  • I ran each SELECT several times and kept the best time for each, not average time.

SQLCLR Test and Results:

DECLARE @Test1 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test1 =  -- Comment out "@Test1 = " to test returning the value
       SQL#.INET_AddressToNumber(CONVERT(NVARCHAR(15), tmp.[IPAddress]))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2454 ms,  elapsed time = 5133 ms.
-- CPU time = 1594 ms,  elapsed time = 1617 ms. (into variable)

MartinSmith's Test and Results:

DECLARE @Test3 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test3 =  -- Comment out "@Test3 = " to test returning the value
         256 * 256 * 256 * CAST(FLOOR(LEFT(tmp.[IPAddress],3)) AS BIGINT)
       +       256 * 256 * CAST(FLOOR(SUBSTRING(tmp.[IPAddress],1 + CHARINDEX('.', tmp.[IPAddress]),3)) AS BIGINT)
       +             256 * SUBSTRING(REPLACE(tmp.[IPAddress],'.',SPACE(8)),19, 9)
       +                   RIGHT(tmp.[IPAddress], -1 + CHARINDEX('.', REVERSE(tmp.[IPAddress])))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2781 ms,  elapsed time = 5151 ms.
-- CPU time = 2156 ms,  elapsed time = 2156 ms. (into variable)

O.P.'s Test and Results:

DECLARE @Test2 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test2 =  -- Comment out "@Test2 = " to test returning the value
       ((256*256*256) * CONVERT(BIGINT, LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),1, 9)))))
       + ((256*256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),10, 9)))))
       + ((256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),19, 9)))))
       + (CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),28, 9)))))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 3531 ms,  elapsed time = 5249 ms.
-- CPU time = 2515 ms,  elapsed time = 2534 ms. (into variable)

One thing to keep in mind regarding the SQLCLR option: using a SQLCLR function in a computed column is a dependency that will prevent the Assembly containing the function from being dropped. If you are able to use ALTER ASSEMBLY, then you won't being dropping and re-creating the Assembly. But ALTER ASSEMBLY is only allowed if there are no new or removed methods, and if the signatures of existing methods are still the same. If any of those changes are being made, then you will have to drop the Assembly and then create it again. And that will require that you first drop the computed column that is referencing the SQLCLR function contained in that Assembly. This might make the SQLCLR option less desirable in the use case of placing a function in a computed column, whereas if the function was in a query, Stored Procedure, View, etc, then this wouldn't be a concern.

It is possible to wrap the SQLCLR function in a T-SQL function and reference the T-SQL function in the computed column, but then you lose the benefit of SQLCLR functions being able to participate a parallel execution plan.

5
  • 1
    @MartinSmith That's one of the nifty things about SQLCLR: UDFs that are marked as IsDeterministic=true, and have not been marked as having either user or system data access, can participate in parallel plans, unlike T-SQL UDFs. I have not tested / proven that this holds true for the computed column scenario, but my expectation is that it would still be true. Oct 9, 2016 at 20:39
  • 1
    Thanks for the info. I wasn't sure if the parallel blocking applied to those too. Oct 9, 2016 at 20:44
  • wow, thanks @srutzky for taking the time to run the performance tests for each scenario. I wouldn't have expected the outcomes to be so close together in terms of elapsed time. For me the 1,000,000 row test is right about where my current dataset lies. How does the SQLCLR compare if you are only using 1,000 rows or less? Though that is probably an insignificant analysis.
    – Devon Ward
    Oct 11, 2016 at 15:44
  • @DevonWard You're welcome. If I'm going to suggest using SQLCLR, then I kinda need to make sure that there is a benefit in it and that the performance isn't worse, especially given that you already have a working T-SQL approach, and Martin was able to come up with a slightly better pure T-SQL approach. There is a downside, however, to SQLCLR in that you might have to drop and re-add the persisted computed column IF you made changes to assembly such as adding/removing methods or changing signatures. That can be annoying. I will update my answer with this caveat. I will try testing 1000 rows. Oct 11, 2016 at 19:18
  • @DevonWard I updated my answer to include that drawback, in this particular use-case (when using a SQLCLR function in a computed column), at the bottom of the answer. Also, I did run the test with 1000 rows, and as expected, there was little to no difference between the options. Oct 12, 2016 at 5:28
3

Another alternative would be the following.

The number of CHARINDEX operations is kept somewhat manageable by not attempting to find the start and end position for each octet. It just finds the start position and extracts the next three characters then uses FLOOR to discard any dot (and any subsequent character to that.)

The third octet was still pretty messy though so I used the approach in your question for that with a minor simplification that replicate(' ',8) can be replaced with space(8)

CREATE TABLE Ip4Address
(
Dotted VARCHAR(15) CHECK (Dotted NOT LIKE '%[^.0-9]%' COLLATE Latin1_General_100_BIN2 /*Only dots and digits*/
                                 AND Dotted NOT LIKE '%..%'          /*No sequences of 2 or more dots*/
                                 AND Dotted NOT LIKE '%[0-9][0-9][0-9][0-9]%'          /*No sequences of 4 or more digits*/
                                 AND LEN(Dotted) - LEN(REPLACE(Dotted, '.', '')) = 3), /*exactly 3 dots*/

Integer AS   256 * 256 * 256 * CAST(FLOOR(LEFT(Dotted,3)) AS BIGINT)
           +       256 * 256 * CAST(FLOOR(SUBSTRING(Dotted,1 + CHARINDEX('.', Dotted),3)) AS BIGINT)
           +             256 * SUBSTRING(REPLACE(Dotted,'.',SPACE(8)),19, 9)
           +                   RIGHT(Dotted, -1 + CHARINDEX('.', REVERSE(Dotted))) PERSISTED
)

Is this method more efficient?

Possibly.

A quick test showed the following

+---------------------------------------------------+-------------------+--------------------------------+
|                      Action                       | Time Elapsed (ms) | Time Elapsed per address ( μs) |
+---------------------------------------------------+-------------------+--------------------------------+
| Generating 10 million dotted IP addreses          |             37586 |                         3.7586 |
| Generating 10 m and running my calculation        |            106106 |                        10.6106 |
| Generating 10 m and running original  calculation |            119732 |                        11.9732 |
+---------------------------------------------------+-------------------+--------------------------------+

so it averaged 1.36 microseconds faster per row in my test so it seems like a micro optimisation not worth spending time on IMO.

Regarding efficiency you could consider not persisting it at all. It doesn't need to be PERSISTED to be indexed. Just deterministic. The index does of course persist it anyway but it won't bloat up the row size in the data pages.


Test script used

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L3),
  Dotted AS (select  TOP 10000000  CONCAT(N1.N,'.',N2.N,'.',N3.N,'.',N4.N) AS Dotted
from nums N1, nums N2, nums N3, nums N4)
/*
Query 1 - baseline 
SELECT MAX(Dotted) FROM Dotted
*/
/*
Query 2 
SELECT MAX(
256 * 256 * 256 * CAST(FLOOR(LEFT(Dotted,3)) AS BIGINT)
           +       256 * 256 * CAST(FLOOR(SUBSTRING(Dotted,1 + CHARINDEX('.', Dotted),3)) AS BIGINT)
           +             256 * SUBSTRING(REPLACE(Dotted,'.',SPACE(8)),19, 9)
           +                   RIGHT(Dotted, -1 + CHARINDEX('.', REVERSE(Dotted))) 
)
FROM Dotted
*/
/*
Query 3
SELECT MAX(((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),28, 9))))))
FROM Dotted  --2:07 4295072150
*/
4
  • I like your use of floor() for the first two octets!
    – Devon Ward
    Oct 8, 2016 at 20:41
  • The OP asked for more efficient methods, but you make no mention of any performance testing for this. Is it more efficient? Oct 8, 2016 at 22:57
  • 1
    @MisterMagoo the question asks "What are the various ways" I've provided one, the OP can test it themselves if they care about that. I don't. Personally I doubt switching between any vaguely sensible inline method will make a significant contribution to the cost of the surrounding update or insert. Oct 9, 2016 at 1:54
  • @MisterMagoo and Martin: you are both correct regarding what the O.P. has requested. Martin quoted the first sentence which doesn't mention performance. But the last sentence of the question is: "What other more efficient techniques are there out there?". So +1 as this is both another technique and more efficient :). Oct 9, 2016 at 20:42
0

Given the constraints you're under with the computed column, that's probably the best you'll do. Constructing the same function using CHARINDEX and SUBSTRING would be even uglier IMO.

Be sure to leave a comment in your version control system for any future maintainers. That's not the most obvious way of splitting a string.

0
0

Consider Using Calculated Field Within a Table

I don't know if this is the most efficient, but it sure was fun to write and I can't imagine writing it being any easier.

Here I have the table tbl_IPv4 do all the work of making the conversion via a calculated field. I simply transformed the IP address into a select statement so it could be used to insert into the tbl_IPv4 table by (add a SELECT/change periods to commas). Sooo... if you're inserting IP addresses and need an on-the-fly conversion without a nightmarish calculated field--this might work for you. Note, the 256.0 is carefully placed in the first octet so SQL won't return an arithmetic overflow error.

CREATE TABLE [dbo].[tbl_IPv4](
    [oct1] [char](8) NULL,
    [oct2] [char](8) NULL,
    [oct3] [char](8) NULL,
    [oct4] [char](8) NULL,
    [IPv4Total]  AS ((((((256.0)*(256))*(256))*[oct1]+((256)*(256))*[oct2])+(256)*[oct3])+[oct4]) PERSISTED
) 


    declare @ipv4 varchar(255)= '255.255.255.255'
    set @ipv4 = 'select ' + replace(@ipv4, '.',',')
    insert into tbl_IPv4
    exec (@ipv4)
    select * from tbl_ipv4

I used PERSISTED to stop calculating and store those values instead and even index them. It even shows that this method is deterministic.

Per Microsoft:

PERSISTED Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

p.s. SQL Server delightfully added all the parenthesis and brackets to my calculated field. You can test the calculated field as follows:

select 256.0*256*256*oct1 +256*256*oct2 + 256*oct3 + oct4 from tbl_ipv4 

And perhaps for even less calculations and less readability you could write or use in the calculated field in tbl_IPv4:

select 16777216.0*oct1 +65536*oct2 + 256*oct3 + oct4 from tbl_ipv4 
6
  • Why the first one is 256.0? Isn't int or bigint good? Oct 10, 2016 at 17:58
  • Unfortunately, things like select convert(bigint,256*256*256*oct1) from tbl_ipv4 throws an arithmetic error in SQL
    – Sting
    Oct 10, 2016 at 18:06
  • What about 256*256*256*convert(bigint,oct1) from tbl_ipv4? Oct 10, 2016 at 18:24
  • 1
    This would be fine if the O.P. had each octet in a separate column, but I think it is clear from the wording of the question that the O.P. already has IP Address values in a single column. And the O.P. has considered a persisted computed column as the question states: "in order to create a persisted calculated column". Also, while using 256.0 does work, that converts to a FLOAT. It would have worked via CONVERT(BIGINT, 256) * other stuff. Or, regarding your last example: CONVERT(BIGINT, 16777216) * oct1. Oct 10, 2016 at 18:24
  • 1
    @Sting No problem. Wasn't trying to be negative, just clarify based on my reading of things :). And I believe that the BIGINT route is better given the "imprecise" nature of FLOAT, but shouldn't be a huge issue when multiplying whole numbers. From what I have seen, FLOAT issues usually come into play when dividing numbers, or computing fractional values, even via addition. And yes, your way is good food for thought. I would even go one step farther and store each octet in a TINYINT column since it has the same 0-255 range :). Then it's only 1 byte and already a number. Oct 10, 2016 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.