See here for font conventions used in this article. This article covers the topic in depth, and you would read it if you have an “binary option guru jim” scenario or you are more the geeky sort of person.
In the majority of the cases, people have a comma-separated list, because this format is produced by commonly used tools like multi-choice controls in . NET, Reporting Services and other places. You can find no end of blog posts etc that presents string-splitting functions, including performance tests of such functions and there are function that are known to be the fastest etc. Give a general discussion of how to design string-splitting functions. Present and discuss each method from the angles I bring up in the general discussion.
This includes performance, but not only. To back up my discussion on performance, I have run extensive performance tests, although it was a while ago now, and they don’t cover the new possibilities that were added in SQL 2016. I have published the results from these tests in two appendixes. I published the first version for SQL 2000 already in 2003 and I updated it several times for several years with the last major update in 2010.
What I find surprising as I write this new introduction in 2016 is that the topic still is current. After all, it was already SQL 2008 that gave us what I thought would be the ultimate solution: table-valued parameters. Nevertheless, questions on how to handle CSVs keep coming up in the forums, and partly this is to blame on the sad fact that not all environments support table-valued parameters. So what you have here is the 2016 version of this article. I have moved the material for beginners to the short version.
I have also conducted a some general overhaul of the contents in general to modernise it a bit. For instance, it was time to stop talking about features added in SQL 2005 as new features! Another topic I touch in this article is how to insert many rows efficiently. While this is something that is best done with table-valued parameters, some form of bulk load or SSIS, some of the methods in this text are interesting from this perspective as well. You can download the script to install it from Microsoft’s web site. Overview of the Methods As I’ve already hinted there are quite a few methods to unpack a list into table. Here I will just give a quick overview of the methods, before I move on to the general considerations.
Looping through a comma-separated list, and returning the elements in a table. While being one of the slower methods, performance is still acceptable for most applications. Its main advantage is that it is easy to understand and you can easily modify it to handle different input formats. NET, and this is a lot faster.
Obviously it is nice that it is built-in and always there. But it also has some shortcoming and gotchas. XML is the prime choice when you need to insert many rows at one time and you cannot use TVPs. Using XML for a list of single values is a bit of overkill, but it works. Not mastering JSON myself, I’m covering JSON very briefly. Using a Table of Numbers to unpack a comma-separated list. A relationally “pure” solution, that is not far behind the CLR in performance.
Rather than using a comma-separated list, use a string where all elements have the same length. This method also uses a table of numbers. Variations of Table of Numbers and Fixed-length where the number are generated in a function rather than taken from a table. If you want a method that does not require any extra support such as the CLR or a table of numbers, and maybe not even a function, this method is a good choice. Performance is not marvellous, but better than for the iterative method. This is generally the best method as long as your environment supports them. In this article, I only discuss TVPs from a performance perspective compared with the other methods in this article.
For a list of numbers, it may appear simpler than any other method, but there are several complications with regards to security. This method is also slower than most other methods, particularly for long input. I discuss this method only to have you to stay away from it. Passing the List as Many Parameters. Simply pass each list element as a separate parameter. Has an upper limit of 2100 list elements at which performance is anything but fantastic. This method does not really have any advantage for handling comma-separated lists, but it is an interesting alternative when you need to insert many rows.