Posted by on February 20, 2018

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).

Comments

Be the first to comment.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: