String_Split Performance

Kevin Feasel

2016-03-21

T-SQL

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 (coughFORMAT() 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 MAX types 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.

Related Posts

Validating SSIS Packages Using T-SQL

Annie Xu shows us how to validate SSIS packages in the SSISDB catalog using T-SQL: Recently, I need to do a data warehouse migration for a client. Since there might be some difference between the Dev environment source databases and Prod environment source databases. The migrated SSIS packages for building data warehouse might have some […]

Read More

APPROX_COUNT_DISTINCT

Niko Neugebauer is happy with a new function in SQL Server 2019: A rather interesting result takes place if we scale our database to 100GB TPCH and run the very same queries – the total elapsed time jumps to 50% difference (from 30%), the CPU execution time difference is kept at 50%, but the memory […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031