Updating multiple columns in sql
Over on SQLServer Central, Jeff Moden presented a splitter function that rivaled the performance of CLR, so I thought it only fair to include a variation using a similar approach in this round-up.
I had to make a few minor changes to his function in order to handle our longest string (500,000 characters), and also made the naming conventions similar: And in fact, these are the results we see in all five cases…
This is the problem where people want to pass in a string like this: But since there is no array type in SQL Server, this is not how the variable is interpreted at all – it's still a simple, single string that happens to contain some commas.
He spent a lot of time fine-tuning this CLR function to efficiently parse a string.
I use a #temp table so that I can simply use GO Creating and populating this table took about 20 seconds on my machine, and the table represents about 6 MB worth of data (about 500,000 characters times 2 bytes, or 1 MB per string_type, plus row and index overhead).
Not a huge table, but it should be large enough to highlight any differences in performance between the functions.
For this specific use case, the Numbers table consistently performed the worst: After the hyperbolic 40-second performance for the numbers table against 10 rows of 50,000 characters, I dropped it from the running for the last test.
To better show the relative performance of the four best methods in this test, I've dropped the Numbers results from the graph altogether: Next, let's compare when we perform a search against the comma-separated value (e.g.
In this case the compressed data requires 1,360 pages, versus 2,102 pages without compression – about a 35% savings.) This solution uses a recursive CTE to extract each part of the string from the "remainder" of the previous part.