SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that’s the only thing they improved – there’s still a critical performance problem with it.

Let’s take the Stack Overflow database, Users table, put in an index on Location, and then test a couple of queries that use STRING_SPLIT to parse a parameter that’s an incoming list of locations:

CREATE INDEX Location ON dbo.Users(Location);SET STATISTICS IO ON;GOCREATE OR ALTER PROC dbo.usp\_GetUsersByLocation\_Subquery@LocationList NVARCHAR(4000) ASSELECT TOP 1000 u.*FROM dbo.Users uWHERE u.Location IN (SELECT value FROM STRING\_SPLIT(@LocationList, N',', 1))ORDER BY u.Reputation DESC;GOCREATE OR ALTER PROC dbo.usp\_GetUsersByLocation\_Join@LocationList NVARCHAR(4000) ASSELECT TOP 1000 u.*FROM STRING\_SPLIT(@LocationList, N',', 1) lINNER JOIN dbo.Users u ON l.value = u.LocationORDER BY u.Reputation DESC;GOEXEC usp\_GetUsersByLocation\_Subquery N'India,China';EXEC usp\_GetUsersByLocation\_Join N'India,China'; The two queries produce slightly different actual execution plans, but the way STRING_SPLIT behaves is the same in both, so I’m just going to take the first query to use as an illustration:

That red-highlighted part has two problems:

1. SQL Server has no idea how many rows are going to come out of the string, so it hard-codes a guesstimate of 50 items
2. SQL Server has no idea what the contents of those rows will be, either – it doesn’t know if the locations are India, China, or Hafnarfjörður

As a result, everything else in the query plan is doomed. The estimates are all garbage. SQL Server will choose the wrong indexes, process the wrong tables first, make the wrong parallelism decisions, be completely wrong about memory grants, you name it.

Like I wrote in this week’s post about DATETRUNC, that doesn’t make STRING_SPLIT a bad tool. It’s a perfectly fine tool if you need to parse a string into a list of values – but don’t use it in a WHERE clause, so to speak. Don’t rely on it to perform well as part of a larger query that involves joins to other tables.

Working around STRING_SPLIT’s problemsOne potential fix is to dump the contents of the string into a temp table first:

CREATE OR ALTER PROC dbo.usp\_GetUsersByLocation\_TempTable@LocationList NVARCHAR(4000) ASBEGINSELECT valueINTO #LocationListFROM STRING\_SPLIT(@LocationList, N',', 1);SELECT TOP 1000 u.*FROM dbo.Users uWHERE u.Location IN (SELECT value FROM #LocationList)ORDER BY u.Reputation DESC;ENDGOEXEC usp\_GetUsersByLocation\_TempTable N'India,China'; And the actual execution plan is way better than the prior examples. You can see the full plan by clicking that link, but I’m just going to focus on the relevant STRING_SPLIT section and the index seek:

This plan is better because:

• SQL Server knows how many rows are in #LocationList
• Even better, it knows what those rows are, and that influences its estimate on the number of users who live in those locations, which means
• SQL Server makes better parallelism and memory grant decisions through the rest of the plan

Woohoo! Just remember that temp tables are like OPTION (RANDOM RECOMPILE), like I teach you in this Fundamentals of TempDB lecture.