Aaron Bertrand looks into how STRING_SPLIT performs compared to other string splitting methods:
So with those limitations out in the open, we can move on to some performance testing. Given Microsoft’s track record with built-in functions that leverage CLR under the covers (cough
FORMAT()cough), I was skeptical about whether this new function could come close to the fastest methods I’d tested to date.Let’s use string splitters to separate comma-separated strings of numbers, this way our new friend JSON can come along and play too. And we’ll say that no list can exceed 8,000 characters, so no
MAXtypes are required, and since they’re numbers, we don’t have to deal with anything exotic like Unicode.
The results are surprising. I expected it to be somewhere around CLR-level, but not way better.