3

The following query converts a single string of packed CSV that represents 13k rows by 2 columns. Column A is a bigint. Column B is a smallint.

declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
      select Value,
             Row = row_number() over (order by (select null)) - 1
      from string_split(@dataCsv, ',') o
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         convert(bigint, i.Value) as ObjectId,
         convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

Execution Plan: https://www.brentozar.com/pastetheplan/?id=By0hYPmd6

As shown in the plan, the evaluation of the convert() is occurring before the join so sometimes (depending on length of input), it fails with

The conversion of the nvarchar value '37645' overflowed an INT2 column. Use a larger integer column.

Temporarily changing the conversion from smallint to int allows the query to complete and inspecting the BrandId column output shows that it always just contains the value '36' for this example.

Is there a simple way to delay the convert(smallint, i2.Value) until the after the join so that only those expected CSV positions are converted?

I realize there are other ways to pack a string stream (such as using multiple variables, or interlacing different split chars, etc) but am not interested in solving this example that way for purposes of this question. Thanks!

2 Answers 2

6

If you want to use this self join approach I'd just use try_convert so you aren't dependent on where the convert gets evaluated.

TRY_CONVERT(smallint, '37645') returns NULL rather than error so it doesn't matter if that happens in a row subsequently filtered out.

Also your approach to calculate an ordinal from string_split is not reliable.

The output rows might be in any order. The order isn't guaranteed to match the order of the substrings in the input string.

And even if that wasn't the case order by (select null) would still not guarantee anything.

Assuming you aren't on SQL Server 2022 (so the enable_ordinal argument isn't available to you) you can tweak the CSV to get it into JSON array format and get supplied an ordinal that way.

declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
    SELECT Value,
           Row = CAST([key] AS INT)
    FROM OPENJSON(CONCAT('[', @dataCsv, ']'))
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         try_convert(bigint, i.Value) as ObjectId,
         try_convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

But my suggestion would be to drop the self join entirely and use conditional aggregation.

In this case if you are certain that every second element in the string will be a valid smallint you can revert to convert. The CASE expression should prevent it being evaluated on the values where ordinal %2 does not evaluate to 1.

DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows

WITH Input
     AS (SELECT Value,
                Row = [key] / 2,
                Col = [key] % 2
         FROM   OPENJSON(CONCAT('[', @dataCsv, ']')))
--insert into PubCache.TableName
SELECT CacheId = 78064,
       ObjectId = MAX(CONVERT(BIGINT,  CASE WHEN Col = 0 THEN Value END)),
       BrandId = MAX(CONVERT(SMALLINT, CASE WHEN Col = 1 THEN Value END))
FROM   Input i
GROUP  BY Row
ORDER  BY Row 
1
  • The Condition Aggregation appears to more extendable and performant to N columns as well.
    – crokusek
    Jan 5 at 0:57
1

PIVOT is better suited for changing one column into multiple columns than either self joins or grouping. With PIVOT, you would write your query in any of the following two ways.

Using STRING_SPLIT with enable_ordinal, if available:

DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows
WITH input AS (
    SELECT value, 
        (ordinal + 1) / 2 row, 
        ordinal % 2 col
    FROM STRING_SPLIT(@dataCsv, ',', 1)
)
--insert into PubCache.TableName
SELECT 78064 CacheId,
    CAST([1] AS bigint) ObjectId,
    CAST([0] AS smallint) BrandId
FROM input 
PIVOT (MAX(value) FOR col IN ([0], [1])) As pvt

Alternatively, using OPENJSON, as pointed out by @martin-smith:

DECLARE @dataCsv NVARCHAR(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows
WITH input AS (
    SELECT value, 
        [key] / 2 row, 
        [key] % 2 col
    FROM OPENJSON(CONCAT('[', @dataCsv, ']'))
)
--insert into PubCache.TableName
SELECT 78064 CacheId,
    CAST([0] AS bigint) ObjectId,
    CAST([1] AS smallint) BrandId
FROM input 
PIVOT (MAX(value) FOR col IN ([0], [1])) As pvt
1
  • PIVOT is largely syntax sugar for the grouping. The execution plans are much the same. On the positive side it suppresses the "Warning: Null value is eliminated by an aggregate or other SET operation." message though! Jan 4 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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