Cross apply: Select vs Values

112 Views Asked by At

Is there any reason to use values vs select in a cross apply statement? I've heard people reference this technique more often than not as "cross apply with VALUES". I find using selects to be more readable. Is there an advantage to one or the other?

Below is an example. Please note, I don't need help with what the code is doing, just trying to understand if one version is optimal, and why.

Using VALUES:

-- Using values in the cross aply
;with splitMeBabyOneMoreTime (value) as
(
    select 'a/b/c/d/e' union all
    select 'd/e/f/g/f'
)
select *
from splitMeBabyOneMoreTime a
cross apply
(
    values (charindex('/', a.Value)) 
) b (Index1)
cross apply
(
    values (charindex('/', a.Value, b.Index1 + 1))
) c (Index2)
cross apply
(
    values (charindex('/', a.Value, c.Index2 + 1))
) d (Index3)

Using Select:

-- Using select in the cross apply
;with splitMeBabyOneMoreTime (value) as
(
    select 'a/b/c/d/e' union all
    select 'd/e/f/g/f'
)
select *
from splitMeBabyOneMoreTime a
cross apply
(
    select Index1 = charindex('/', a.Value)
) b 
cross apply
(
    select Index2 = charindex('/', a.Value, b.Index1 + 1)
) c
cross apply
(
    select Index3 = charindex('/', a.Value, c.Index2 + 1)
) d 
0

There are 0 best solutions below