When STRING_SPLIT() falls short
Tags: sql, sqlserver, performance
Published: 2020-03-20 14:06:56

---
title: When STRING_SPLIT() falls short
published: true
date: 2020-03-20 13:58:54 UTC
tags: sql,sqlserver,Performance
cover_image: 
canonical_url: https://am2.co/2020/03/when-string_split-falls-short/
---

This morning, my friend Aaron Bertrand ([blog](http://sqlblog.org)|[twitter](https://twitter.com/AaronBertrand)) posted [a plea for help with `STRING_SPLIT()` improvements](https://sqlperformance.com/2020/03/t-sql-queries/please-help-string-split-improvements). I’m very much in Aaron’s corner on this, having [written about the olden days myself](https://am2.co/2015/04/converting-delimited-values-to-a-table-efficiently/). 
### Why `STRING_SPLIT()` is supposed to be awesome

CLR was fast, but inaccessible to many DBAs. XML was almost as good, but ugh, XML. And multi-statement table valued functions are evil but prevalent.

`STRING_SPLIT()` promised to be both super fast, and lift the code maintenance problem for DBAs who don’t know .NET and struggle with the crazy XML syntax.

Except, as Aaron explains, it falls short on everything but the most basic scenario.

[![](http://am2.co/wp-content/uploads/STONE_SPLIT.jpg)](https://www.youtube.com/watch?v=vY1TQ9pb2J4)<figcaption id="caption-attachment-2828"><a href="https://twitter.com/AaronBertrand" rel="noopener noreferrer" target="_blank">Aaron</a> attempting a STONE_SPLIT at a recent curling competition.</figcaption>

  
### My anecdote

At a prior gig, I helped support a database that backed an app written with (older versions of) Java & Hibernate. Java/Hibernate don’t support [table-valued parameters](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql), so it was a very common scenario to pass in a CSV of ID values, and split it in the database. Hibernate’s alternative is to parameterize the _single_ ID, then iterate row by agonizing row over the set–which is painful and an even worse choice than a table-valued multi-statement split function.

After upgrading to SQL Server 2017 (my first project of the gig), we started moving our biggest “problem queries” to stored procedures, and making use of `STRING_SPLIT()`. Hibernate would continue to pass in its `[n]varchar` CSV of values, and then in the stored procedure, we’d throw in a `CROSS APPLY STRING_SPLIT(@csv)` or `WHERE SomeID IN STRING_SPLIT(@csv)`. In most cases, we found that this approach didn’t give us the big awesome performance gain we were looking for. In some cases, the query got worse!

#### Worse, you ask?

Yep. Worse. I’m a little fuzzy on the details, and too busy/lazy to build the repro (if you have one, send it along & I’ll give you the credit for picking up my slack!), but here’s the gist. The `@csv` value might be a couple of hundred characters long, which would cause the function to use a return type of `varchar` with a length matching `@csv`. This made for some wonky memory grants, and added implicit conversions. While the implicit conversions went in the right direction (`varchar`–>`int`), it’s an extra step. And every extra step increases the number of possible plans, and increased the chances that you get a suboptimal plan–and we got suboptimal plans in spades!

We were also occasionally being bit by duplicate values in the CSV being returned twice in the result set–an application bug, sure, but one that the prior homemade function defended against.

The workaround in our case was easy. At the start of the procedure, we’d have something like this:

```sql
DECLARE @IdList TABLE (Id int);

INSERT INTO @IdList (Id)
SELECT DISTINCT value
FROM STRING_SPLIT(@csv);
```

Then, we’d use `@IdList` in the query, rather than an inline `STRING_SPLIT()`. Sure, its not that difficult of a workaround, but when you’ve got THOUSANDS of places where you’re looking to drop in this (theoretically) better-performing option, the extra step adds to complexity & slows down adoption.

### Go vote!

Go vote on [Aaron’s UserVoice post](https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete), but don’t JUST vote. Compelling user stories are the most valuable thing in convincing the SQL Server Product Team to make a change. Tell your own story for what `STRING_SPLIT` enhancements are important to you, either on [Aaron’s blog post](https://sqlperformance.com/2020/03/t-sql-queries/please-help-string-split-improvements), or on this one.

The post [When STRING\_SPLIT() falls short](https://am2.co/2020/03/when-string_split-falls-short/) appeared first on [Andy M Mallon - AM²](https://am2.co).