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