Splitting A Delimited String (Part 2)

This is part two of a two part series.  In part 1 of this series I demonstrated the most popular methods used to parse/split a delimited string of values, http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html.  In this article I will be focusing on the performance implications of each method presented.  I will start of by giving a disclaimer that your results may vary from the results presented in this article.  Although the numbers may differ, the data trend should be somewhat consistent with my results.  I will be tracking three key performance counters: CPU, Duration, and Reads against varying string sizes and data loads. In addition to a varying delimited strings length and load, I have performed each query 10 times and taken an average.  I did this to ensure that I get the most accurate results. Enough talk let’s dive into our first test.

Note: I am not going to walk through how I did each test, but I will link all my scripts at the bottom of this post

The first test is testing how CPU usage differs between methods.  I tested a delimited string of exactly 10 Ids over a table with 10,000 rows, 100,000 rows and 1,000,000 rows.  As you can see, the permanent numbers table TVF is by far the best solution.  The CPU is highest on the inline Numbers TVF.  The inline numbers table is the most expensive because SQL Server has to do a lot of processing and calculation on the fly, whereas the permanent numbers table mostly has to read data, which means the IO will be much higher.  Both XML methods perform much better than the inline numbers table, but are nearly twice as slow as the permanent numbers TVF because they require more CPU intensive processing, which is derived from SQL converting the data to XML and transforming the XML back to a relational format.  It takes more processing power to encode and decode the XML than to simply convert it, so you should only use the encode/decode method, if your data contains special XML characters, http://msdn.microsoft.com/en-us/library/aa226544(SQL.80).aspx.

Winner of Round 1: Numbers Table Split Function

The next test was performed on a table with 10,000, 100,000 rows, 1,000,000 rows, using a string consisting of 100 Ids.  If you look at the chart below, you will see a trend.  As the number of Ids increases, so does the cost of the XML method.  CPU usage actually increases at a exponential rate, which makes it the least scalable solution.  Obviously the Numbers Table TVF is the clear winner here. 

Winner of Round 2: Numbers Table Split Function

The final test was taken over the same load, but I supplied a string of 1000 Ids.  As you can see with 1000, ids the results depict more of the same behavior.  The big take away is the XML method should only be used when the number of values in the delimited string is relatively small.

Winner of Round 3: Numbers Table Split Function

The next counter I will be focusing on is Duration.  Duration is not a very reliable counter, as it is very dependent on other processes running on the machine; however, it does provide insight to performance.  The first test will be done over the same load.  I will begin with 10 Ids again.

The results are a little more aligned in this test.  The Numbers Table Split TVF is the best performing on average, followed by the XML methods.  Again there is a higher performance cost to encode XML, so do so only when necessary.  Duration does give you a general idea about performance, but these results definitely do not carry as much weight as the other counters.

Winner of Round 4: Numbers Table Split Function

The next step is to increase the number of Ids to 100.  I wont repeat the same stuff over again I promise.  This test yields more of the same.

Winner of Round 5: Numbers Table Split Function

Next, I bump the number of Ids to 1000.  Here we go….. a different result :) .  In this example, the numbers table actually performed worse than the inline number TVF.  You may be wondering why the duration is worse for the numbers table split function.  I suspect the answer is the number of reads makes the query take longer to execute; however, there could have been something running on my machine when profiler captured the data.  This is a prime example of why duration can be an unreliable counter. I cannot tell if the query is actually worse or if an environmental factor on my laptop may have skewed the timing.  I will take the high road :^) and assume the reads impacted the timing because I had no known programs running.   The XML results are just disturbing…… For as much as I recommend XML split solutions on the forums, these results are just scary.

Winner of Round 6: Inline Numbers TVF Split

The final counter I will be testing is reads.  This is by far one of the most important counters because it impacts so many facets of SQL Server performance. Do not let the number of reads for the Numbers Table TVF persuade you to avoid it. A permanent numbers table TVF  is going to have more reads.  Essentially you are reading  table values from cache/disk instead of calculating them, so the numbers of reads is greater.  The obvious choice for this test is the inline numbers table TVF.

Winner of Round 7: Inline Numbers TVF Split

The next test increases the number of Ids to 100.  As for the results, we see more of the same.

Winner of Round 8: Inline Numbers TVF Split

Finally, I will increase the number of Ids to 1000. Again more of the same.  The number or reads, stays relatively consistent across all solutions.  The XML solution does better than the numbers table TVF here, but it just does not scale well at any other level.  I used to be a firm believer in the XML method, but I think I am going to start primarily recommending the number table TVF or an inline number table TVF.

Winner of Round 9: Inline Numbers TVF Split

The verdict

So what is the verdict?  Well, again I cannot reiterate enough that no one solution is always better than another.  Different solutions work best in different situations and environments.  In my tests, there is a clear winner, the permanent numbers table TVF.  Even though this is the “winner”, I have overwhelming evidence that says I should be using a numbers table to split a string, regardless of the numbers table being permanent or inline.  I am happy with these results because the permanent numbers table split function performs well and is very easy to implement.  Another benefit of the permanent numbers table TVF solution is that it works in all versions of SQL.  Why would you not want to use the permanent numbers table?  You may be willing to accept more CPU consumption to reduce IO, or perhaps you do not want to maintain another table.  If this is the case, an inline numbers table solution is the way to go.  All-in-all, the XML method really did surprise me  and I find it sad that this method does not perform well.  There is just not enough incentive to use an XML solution when an easier and better performing solution exists.  Remember that you should test each method in your environment to see which works best in your environment. 

I hope that you have learned something and can use this information to make more informed decisions when deciding to find a method to split delimited strings.

**** UPDATE *****

I did have some bugs in the code for the 1000 ids test.  I guess my relaxed brain never came home from vacation.  A special thanks goes out to Brad Schulz for spotting the bug.  I am glad that the end result is for the most part the same.  The only real deviation occurred in the reads category.  The numbers of reads should be comparable for all methods because the same amount of data should be returned, but in my original result set they were not.  I resolved the bug and have since updated the scripts and the results.

****UPDATE*****

A good friend and fellow SQL server enthusiast Brad Schulz recently posted a great entry, on parsing delimited strings with XML.  His findings show how using the XML method in certain ways can cause the query to really bomb; however, you can avoid some performance penalties by casting and storing the delimited string in a XML data type, instead of casting and parsing the XML inline.  I will not go into detail about why the inline XML is slower because I want you to read it right from the horse’s mouth, http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html.   When I changed my code to use a XML variable, the XML methods were just as performant as the numbers table methods.  I do not know about you, but I am ecstatic.  I for one love the XML method and am very excited to see that it is and can be just as performant, when used in the right context.

Download the script files: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/Split%20Delimited%20String/BlogPost^_UnpackDelimitedString.zip

Splitting A Delimited String (Part 1)

In a previous post, I demonstrated how to split a finite array of elements, using XML, http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html.  The method presented in my previous post can only be used when there is a known number of elements.  In this post, I will be focusing on the methods most commonly used today to parse an array, when the number of elements is unknown. This part one of a two part series where I look at the differing methods used to split an array of strings, using SQL Server 2005 and 2008.  There are three primary methods to parse an array.  The first method takes advantage of a numbers table to quickly parse the string.  The second method uses the new XML functionality built into SQL Server 2005.  The final method uses a TVF, without a permanent numbers table. 

Let’s get started by creating are sample table.

SET NOCOUNT ON

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 't')

BEGIN

    DROP TABLE dbo.[t];

END

GO

CREATE TABLE dbo.t(

SomeId INT NOT NULL PRIMARY KEY,

SomeCode CHAR(2)

);

GO

;WITH 

   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows

  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)

  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)

  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)

  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)

  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)

  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  

INSERT dbo.t (SomeId,SomeCode)

SELECT 

    N,

    CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode    

FROM Nums

WHERE N<=10000;

GO

CREATE NONCLUSTERED INDEX ncl_idx_SomeCode ON dbo.t(SomeCode);

GO

The first method I will demonstrate is the Numbers table method.  This is probably the most efficient method of all the methods listed, but performance does vary among environments.  Another great benefit of this method is that it works with SQL Server 2000 and greater.

The first step in using this method is to create a table of numbers.  A table is just what it sounds like… a table of natural number starting from 1 and going to n, where is the maximum number you want in the table.  This method really performs well because of a clustered index on the number column, which allows for very fast index seeks. Here is the code I use to generate my numbers table.

--=============================================================================

--      Setup

--=============================================================================

USE [tempdb]

GO

SET NOCOUNT ON 

GO

--=============================================================================

--      Create and populate a Numbers table

--=============================================================================

--===== Conditionally drop 

IF OBJECT_ID('dbo.Numbers') IS NOT NULL 

BEGIN

    DROP TABLE dbo.Numbers;

END

GO

CREATE TABLE dbo.[Numbers](

N INT NOT NULL

);

GO

;WITH 

   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows

  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)

  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)

  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)

  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)

  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)

  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  

INSERT Numbers

SELECT N FROM Nums

WHERE N<=10000;

GO

ALTER TABLE dbo.Numbers ADD CONSTRAINT PK_N

PRIMARY KEY CLUSTERED ([N])WITH(FILLFACTOR = 100);

GO

Next, we will need to create an Inline TVF (Table Valued Function) to split the array.  This function is written by SQL Server guru Itzik Ben-Gan.  This split function is very fast and very scalable. 

IF OBJECT_ID('dbo.fn_split') IS NOT NULL

DROP FUNCTION dbo.fn_split;

GO

CREATE FUNCTION dbo.fn_split(@arr AS NVARCHAR(2000), @sep AS NCHAR(1))

RETURNS TABLE

AS

RETURN

SELECT

(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,

SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element

FROM dbo.Numbers

WHERE n <= LEN(@arr) + 1

AND SUBSTRING(@sep + @arr, n, 1) = @sep;

GO

This function’s logic is pretty straight forward, but I will discuss how it works.  The numbers table is used to iterate through each character in the array.  As you can see the first step is to pad the beginning of the string with the delimiter.   With all the delimiters in place, the code can determine the position of each delimiter.  Once the logic has the delimiter’s position, the code logics utilizes the CHARINDEX() and SUBSTRING() system functions to extract each element and it’s corresponding position.

Now that we know how this code works, lets see it in action.

DECLARE @Ids VARCHAR(1000)

SET @Ids = '1,500,5439,9999,7453'

SELECT t.*

FROM dbo.t

INNER JOIN dbo.fn_split(@Ids,',') AS fn

    ON t.SomeId = fn.Element

/*

SomeId      SomeCode

----------- --------

1           SQ

500         BO

5439        ZV

9999        RD

7453        IG

*/

Before we move onto the next method, I would like to point out that some developers love to use the exists clause for this situation, especially when the developer does not need any columns from the TVF; however, exists may degrade performance.  I am planning to do an in-depth post regarding the differences between inner join and EXISTS.  To give you an idea of the how exists can degrade performance, have a look at the screenshot below.  Please note that performance is not always black or white and all executions plans will not deviate as much as the one below.

Execution Plan:

IO Stats:

********************* inner join *************************

Table 't'. Scan count 0, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Numbers'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

********************* exists *************************

Table 'Worktable'. Scan count 2, logical reads 20018, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Numbers'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't'. Scan count 3, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see, the difference between the inner join and the exist query plans are night and day.  I will post on this at a later date, but I wanted to make you aware of possible performance problems.

The next method I will be discussing is the XML nodes method.  This method does not require an ancillary table, but does require SQL Server 2005 and greater.  This method does handle XML special characters. I picked up this encoding/decoding method from SQL Server enthusiast Brad Schulz, http://bradsruminations.blogspot.com/.

DECLARE @Ids VARCHAR(1000)

SET @Ids = '1,500,5439,9999,7453'

SELECT t.*

FROM dbo.t

INNER JOIN(

    SELECT x.i.value('.','INT') AS SomeId

    FROM(SELECT XMLEncoded=(SELECT @Ids AS [*] FOR XML PATH(''))) AS EncodeXML

    CROSS APPLY (SELECT NewXML=CAST('<i>'+REPLACE(XMLEncoded,',','</i><i>')+'</i>' AS XML)) CastXML

    CROSS APPLY NewXML.nodes('/i') x(i)

) AS Ids

    ON Ids.SomeId = T.SomeId

The XML method looks a lot more complex than it really is.  Essentially, what I am doing is creating an XML structure that contains each of the elements of the array.  For example, the array “1,500,5439,9999,7453” becomes “<i>1</i><i>500</i><i>5439</i><i>9999</i><i>7453</i>.” The first step is to encode the array by using FOR XML PATH. Once the array is in an encoded string format, I explicitly cast the xml string into an XML data type.  Once I have the XML in a decoding XML format, I use the XML nodes method to put the XML values into a relational format. For more information about how this method works, you can view the following blog post by Brad Schulz, http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html.  This post does a great job of breaking down the inner workings of this method.

The final method I will be demonstrating uses a TVF function with a virtual table of numbers.  The TVF method does not require an ancillary table because a numbers table is generated on the fly.

IF OBJECT_ID('dbo.fn_TVF_Split') IS NOT NULL

DROP FUNCTION dbo.fn_TVF_Split;

GO

CREATE FUNCTION dbo.fn_TVF_Split(@arr AS NVARCHAR(2000), @sep AS NCHAR(1))

RETURNS TABLE

AS

RETURN

WITH 

   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows

  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)

  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)

  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)

  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)

  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)

  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  

SELECT

(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,

SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element

FROM Nums

WHERE 

    n <= LEN(@arr) + 1

    AND SUBSTRING(@sep + @arr, n, 1) = @sep

    AND N<=1000

GO

Now that the function is in place, you can do the same join as before.

DECLARE @Ids VARCHAR(1000)

SET @Ids = '1,500,5439,9999,7453'

SELECT t.*

FROM dbo.t

INNER JOIN dbo.fn_TVF_split(@Ids,',')

    ON t.SomeId = Element

This method is really the same method as provided before, except it does not take advantage of a permanent numbers table. 

I have shown the three most common and best performing methods for splitting a delimited string.  Which method do you use?  As you can imagine, this answer depends on the distribution of data, size of tables, indexes etc..  One method is not always going to be better than another method, so my recommendation is to test each method and choose the one that makes the most sense for you environment. In part two of this series, I am really going to dig into how each of these methods performs on varying sized tables and strings.  This should give you a better idea of which method to choose based on your data, but as stated before the results may vary depending on several environmental factors.

Until next time, happy coding.

Concatenating Column Values (Part 2)

This is the final part of a two part series, where I demonstrate the most commonly used methods to concatenate column values into a delimited string, http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html.   This time around I will be focusing on the performance of each method described in part 1.   The primary methods we are looking at are FOR XML PATH –SubQuery, FOR XML PATH-CrossApply, and a scalar UDF. 

I will be using Profiler to capture performance metrics, as SQL Server IO stats are somewhat unreliable, in regards to UDFs.  The IO statistics are unreliable for scalar UDFs because the  IO does not account for the IO required to obtain the function result.  It only returns the IO resulting from the main query.  I will post an example of this in a future post. Let’s start by creating a new profiler trace.  Open profiler and add the SQL:BatchCompleted counter.  You will only need the columns  textdata, Reads, and CPU.  Add a database filter for ’%tempdb%’ and a filter on the textdata column. The textdata filter should be like ‘%—**%’.  --** is a special string we put in our batch.  This way we can ensure that we only get the statements we want. Now open management studio and make sure to discard the grid results.  You can set this in query options.  Query –> Query Options –> Grid –> Discard results after execution.   Now let’s run our create/test script to generate our table and objects, as shown below.  The main things to note in the below code is the @Batch variable.  This variable dictates how many rows your table will contain.  I ran the code below for 5 different table sizes (100,1000,10000, 100000, and 1000000) 10 times each.  This gives us a pretty solid average, for our tests.  Your results may differ from mine, as there are lots of factors that can influence the result of performance counters, but you should still be in the same ballpark.  In the end I created 10 trace files, each having 10 executions of the code below.  The break down was 5 trace file where a valid predicate was not used and 5 trace files where the predicate was used. Note: The predicate I am referring too is commented out in the below code.  Just uncomment it to run the predicate version of the code.  If you want all the test files I used, you can download them here: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/Concatenating%20Columns%20Part%202/ConcatenateCols%7C_Pt2.zip

SET NOCOUNT ON;

GO

USE [tempdb]

GO

IF OBJECT_ID('dbo.TestData') IS NOT NULL

BEGIN

    DROP TABLE dbo.[TestData];

END

GO

DECLARE @Batch INT

SET @Batch = 1000000 –<-----Number of records in table

SELECT TOP (@Batch)

    SomeID   = ABS(CHECKSUM(NEWID()))%((@Batch/100)*25)+1,

    SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

             + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

INTO dbo.TestData

FROM Master.dbo.SysColumns t1,

     Master.dbo.SysColumns t2 

GO

CREATE CLUSTERED INDEX cl_idx_SomeID_TestData ON [dbo].[TestData](SomeId)

GO

CREATE INDEX IXC_TestData_Cover1 ON dbo.TestData(SomeCode)

GO

USE [tempdb]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConcatenateCols]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[ConcatenateCols]

GO

CREATE FUNCTION dbo.ConcatenateCols(@Id INT)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @RtnStr VARCHAR(MAX)

SELECT @RtnStr = COALESCE(@RtnStr + ',','') + SomeCode

FROM dbo.TestData

WHERE SomeId = @Id

RETURN @RtnStr

END

GO

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

--**

SELECT 

    SomeId,

    dbo.ConcatenateCols(SomeId) AS Cols

FROM [dbo].[TestData]

--WHERE 

--    [SomeId] = 18

GROUP BY

    SomeId

GO

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

--**

SELECT 

    SomeId,

    STUFF(

            (

            SELECT ',' + CONVERT(VARCHAR(5),t2.[SomeCode])

            FROM dbo.[TestData] t2

            WHERE

                t1.[SomeId] = t2.[SomeId]

            FOR XML PATH('') 

            )

    ,1,1,'') AS Cols

FROM dbo.[TestData] t1

--WHERE 

--    [SomeId] = 18

GROUP BY

    SomeId

GO

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

--**

SELECT 

    t1.SomeId,

    MAX(STUFF(t2.x_id,1,1,'')) AS Cols

FROM dbo.[TestData] t1

CROSS apply(

    SELECT ',' + t2.SomeCode

    FROM dbo.[TestData] t2

    WHERE t2.SomeId = t1.SomeId

    FOR xml PATH('')

) AS t2 (x_id)

--WHERE 

--    [SomeId] = 18

GROUP BY

    t1.SomeId

GO

I am not going to show the methods used to load a trace file into a table because that is outside the scope of this post. You can download all the .sql files for this post using the link at the top or bottom of this page.  Essentially, I used the system function to pull in the trace file and I inserted the data into a table in tempdb.  Once the data is in tempdb,  I used the pivot function to pivot the data into a format Excel could use to create a chart.  From there I just had to copy the results and paste them in Excel (to generate the chart).  Okay, now that I have laid all the ground work, let’s get to the numbers.  I will start with the reads for the query that is using a predicate. 

As you can see, the number of reads is very much alike for all three methods. The numbers of reads stays pretty constant for each query across subsequent runs, with differing distributions of data. This result makes sense because the number of reads does not need to change because the optimizer is able to seek the data, which excludes the need to look through additional pages.  The best and worst performing methods in this scenario is quite obvious to see.  The sub query method consistently had lesser reads, while the other methods require more reads.  The cross apply method is by far the worst choice here.  Next let’s look at the the reads for the query without a predicate. 

These results are are closer to what one would expect. Essentially, as the number of rows increases, so does the number of reads.  In this case both the reads and the table size increase by a factor of 10.  The UDF and the sub query methods are neck-and-neck for this test, but if I had to choose  I would say the sub query method wins.  The cross apply method is by far the worst choice again.  Now let’s talk about the CPU usage for each query.

No, real big surprises here.  The CPU time increase exponentially based on the number of rows being processed.  The results are pretty consistent with our previous results.  The sub query is by far the best performing method and the cross apply is the worst.

The CPU times for the query with predicate deviates so little that it is pretty inconsequentially, but notable. I would say the  UDF method is the best  because it has a lesser cumulative CPU time.

So that’s it…. what have we learned?  My take away is the same as it was coming into this test.  You should always test each method before saying any method is better than another.  Performance depends on many factors that may be different in your environment.  Our ultimate goal is to create processes that works best for our environment, so we should never limit our options. If I had to choose a “winning” method for this test, I would choose the FOR XML PATH – sub query method because it consistently has lesser reads and CPU time.

If you want all the files that I used for these tests, you can download them here: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/Concatenating%20Columns%20Part%202/ConcatenateCols%7C_Pt2.zip

Concatenating Column Values (Part 1)

As you may know, SQL Server does not have native support for handing arrays.  This post is part one of a two part series, where I will look at the most widely used methods to concatenate column values.  In this post, I will focus on creating delimited column values and I will explore the performance implications of each method, in the second part of this series.

The two methods that are most often used, in SQL Server 2005+, to concatenate column values are, the variable assignment method and the FOR XML PATH method.  The variable assignment method is most commonly used in scalar UDF's, so there are usually some performance implications.  The FOR XML PATH method is more commonly used in a correlated sub query to concatenate column values inline.  This method also has performance implications.

Let’s create a sample table with data.

USE [tempdb]

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE  NAME = 't')

BEGIN

    DROP TABLE dbo.t;

END

GO

CREATE TABLE t(

id INT,

col CHAR(1)

);

INSERT INTO t VALUES (1,'a');

INSERT INTO t VALUES (1,'b');

INSERT INTO t VALUES (1,'c');

INSERT INTO t VALUES (1,'d');

INSERT INTO t VALUES (2,'e');

INSERT INTO t VALUES (2,'f');

INSERT INTO t VALUES (3,'g');

INSERT INTO t VALUES (4,'h');

We have our data so let’s start with the scalar UDF code.  Essentially we need to create a scalar UDF that takes a surrogate key parameter and will use variable assignment to concatenate all required values.

Here is the Scalar UDF code:

USE [tempdb]

GO

CREATE FUNCTION dbo.ConcatenateCols(@Id INT)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @RtnStr VARCHAR(MAX)

SELECT @RtnStr = COALESCE(@RtnStr + ',','') + col

FROM dbo.t

WHERE id = @Id AND col > ''

RETURN @RtnStr

END

GO

Now that we have our function we can test the scalar UDF code.

SELECT 

    id,

    dbo.ConcatenateCols(id) AS Cols

FROM t

GROUP BY

    Id

Here are the results:

Now let’s take a look at the FOR XML PATH method. 

SELECT 

    Id,

    STUFF(

            (

            SELECT ',' + CONVERT(VARCHAR(5),col)

            FROM t t2

            WHERE

                t1.id = t2.id AND t2.col > ''

            FOR XML PATH('') 

            )

    ,1,1,'') AS Cols

FROM [t] t1

GROUP BY

    Id

Results:

Note: You can also use CROSS APPLY and achieve the same result, but it makes the code a little easier to read, in my opinion, as shown below:

SELECT 

    t1.id,

    MAX(STUFF(t2.x_id,1,1,'')) AS Cols

FROM t t1

CROSS apply(

    SELECT ',' + t2.col

    FROM t t2

    WHERE t2.id = t1.id AND t2.col > ''

    FOR xml PATH('')

) AS t2 (x_id)

GROUP BY

    t1.id

GO

There you have it.  I have shown you the two most widely used methods to concatenate strings in SQL Server 2005+.  They are pretty simple to implement and maintain, but one should know how each method performs, so stay tuned…..in the coming post, I will look at the performance implications associated with the scalar UDF and FOR XML PATH methods.

****************** UPDATE ******************

There was a bug in the code that would cause the concatenated value in the variable assignment method to become NULL.  When I was creating the sample code, I really didn’t think about using COALESCE and a filter predicate because I was using NON NULL values.  Best practice is to use coalesce or a proper predicate filter to prohibit useless data from being concatenated.  I have opted to go with a predicate filter of > ‘’.   Thanks to all for pointing this out.