I recently upgraded a business database at work from SQL Server 2012 to 2016, and increased the compatibility level from 110 to 130. As a result, I can now utilize the new STRING_SPLIT function introduced in 2016 to handle string splitting of parameter values within queries and stored procedures. I noticed considerable performance gains after migrating existing stored procedures from the older approach of using a numbers table paired with a custom string-splitting function.
STRING_SPLIT ( string , separator )
In my business use case, I have an assortment of stored procedures that accept 1 or more nvarchar(max) parameters and are used in various SSRS 2014 reports. Some report parameters support multi-choice values, which I concatenate using a semicolon delimiter and then pass into the stored procedure. The procedure then splits the parameter strings on the semicolon delimiter. Here is an example query:
DECLARE @pOsName NVARCHAR(max) = 'Windows 7;Windows 8;Windows 10;'; DECLARE @pDeptId NVARCHAR(max) = '451;453;454;455;468;469;470;481;'; SELECT E.EntryId, E.EntryName, E.DeptId, E.OsName FROM dbo.Entity E WHERE E.OsName IN ( SELECT value FROM STRING_SPLIT(@pOsName, ';') WHERE value <> '' ) AND E.DeptId IN ( SELECT value FROM STRING_SPLIT(@pDeptId, ';') WHERE value <> '' )
Aaron Bertrand has an excellent (and thorough) analysis of performance versus his older recommended approaches over at sqlperformance.com, along with two follow-up posts that cover a wide range of use cases. At least for my setup, where SSRS report users need to pass multi-value parameters to the SQL server, I am content using the new STRING_SPLIT and enjoying the increase in query performance over my previous approach. I suspect it will be some time before I see an upgraded enterprise SSRS environment, and maybe by then Microsoft will have better parameter support (I am already jealous of parameter layouts in SSRS 2016).