Inline Table

Question

Inline Table

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Jeffrey Williams wrote:

Well - you can avoid this issue using a slightly different approach. I would create an iTVF to split the elements and then cross apply to that function.

  CREATE Function [dbo].[fnSplitString_12Columns] (
        @pString varchar(8000)
      , @pDelimiter char(1)
        )
Returns Table
   With schemabinding
     As
 Return 

 Select InputString = @pString -- v.inputString
      , p01_pos = p01.pos
      , p02_pos = p02.pos
      , p03_pos = p03.pos
      , p04_pos = p04.pos
      , p05_pos = p05.pos
      , p06_pos = p06.pos
      , p07_pos = p07.pos
      , p08_pos = p08.pos
      , p09_pos = p09.pos
      , p10_pos = p10.pos
      , p11_pos = p11.pos
      , p12_pos = p12.pos
      , col_01 = ltrim(substring(v.inputString, 1, p01.pos - 2))
      , col_02 = ltrim(substring(v.inputString, p01.pos, p02.pos - p01.pos - 1))
      , col_03 = ltrim(substring(v.inputString, p02.pos, p03.pos - p02.pos - 1))
      , col_04 = ltrim(substring(v.inputString, p03.pos, p04.pos - p03.pos - 1))
      , col_05 = ltrim(substring(v.inputString, p04.pos, p05.pos - p04.pos - 1))
      , col_06 = ltrim(substring(v.inputString, p05.pos, p06.pos - p05.pos - 1))
      , col_07 = ltrim(substring(v.inputString, p06.pos, p07.pos - p06.pos - 1))
      , col_08 = ltrim(substring(v.inputString, p07.pos, p08.pos - p07.pos - 1))
      , col_09 = ltrim(substring(v.inputString, p08.pos, p09.pos - p08.pos - 1))
      , col_10 = ltrim(substring(v.inputString, p09.pos, p10.pos - p09.pos - 1))
      , col_11 = ltrim(substring(v.inputString, p10.pos, p11.pos - p10.pos - 1))
      , col_12 = ltrim(substring(v.inputString, p11.pos, p12.pos - p11.pos - 1))
   From (Values (concat(@pString, replicate(@pDelimiter, 12))))                 As v(inputString)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, 1) + 1))           As p01(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p01.pos) + 1))     As p02(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p02.pos) + 1))     As p03(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p03.pos) + 1))     As p04(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p04.pos) + 1))     As p05(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p05.pos) + 1))     As p06(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p06.pos) + 1))     As p07(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p07.pos) + 1))     As p08(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p08.pos) + 1))     As p09(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p09.pos) + 1))     As p10(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p10.pos) + 1))     As p11(pos)
  Cross Apply (Values (charindex(@pDelimiter, v.inputString, p11.pos) + 1))     As p12(pos);
GO

Yes, thanks, that's a better way to do it. Of course, the normal way is to use a string splitter function like Jeff's DelimitedSplit8K

It was really just an observation on how SQL Server can expand the formulas in cross applies into really huge expressions, too big even for SQL Server to handle.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

I would disagree with the normal way as using DelimitedSplit8K - if the goal is to split out to individual columns, using that utility would require a secondary pivot/cross-tab (and grouping) to get the data into the columns. It might be a better option - it might not, all depends on the requirements and the strings being parsed.

As for how SQL Server expands out - agreed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

I agree there. "It Depends". Concatenation typically is quite slow but might be worth it here. I have also run into performance issues with cCA's (Cascading Cross Apply's) before but you never know until you try. Only an "insitu" test will let you know for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Jeffrey Williams wrote:

I would disagree with the normal way as using DelimitedSplit8K - if the goal is to split out to individual columns, using that utility would require a secondary pivot/cross-tab (and grouping) to get the data into the columns. It might be a better option - it might not, all depends on the requirements and the strings being parsed.
As for how SQL Server expands out - agreed.

Yes, I think your solution will be faster than using a string splitter function.

When I said "normal" I meant usual in that most solutions would probably use a string splitter (not necessarily a better solution). But I think using cross applies as you have is a better solution in term of perfomance.