I bring up the lack of randomness as it works in the favor of delimitedsplit8k, as the optimizer can basically get to reuse its row plans. (Running the test script I posted on github and removing the the traceflag will cause delimitedsplit to start doing very well against the clr splitter). The fact that you aren't clearing the buffercache also isn't help things. But as I said, I still recommend delimitedsplit8k, if you can't use CLR (and are processing <8k strings obviously).

As for the optimism, I'm just happy to have learned something new. This lag/lead trick can have potential uses in places where you need to generate a lob result in an ITF or View but can't come up with a good way to avoid recomputing the result over and over again. If you remove the lead inner table trick, the million row query will run for an insanely long amount of time.