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; GO CREATE OR ALTER PROC dbo.usp_GetUsersByLocation_Subquery @LocationList NVARCHAR(4000) AS SELECT TOP 1000 u.* FROM dbo.Users u WHERE u.Location IN (SELECT value