Category: SQL Server 2017

How to build comma separated string in SQL Server

Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve this like CLR/XML PATH/Co-related queries etc. With this post, we are going to see two options that are widely used XML PATH & string_AGG and a quick look at its performance comparison.


This is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data.

Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 

SELECT  SessionID,STUFF((SELECT  ',' + CourseID FROM BuildCommaSeparatedString EE
            WHERE  EE.SessionID=E.SessionID
            ORDER BY CourseID -- To order the Courses in order 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM BuildCommaSeparatedString E

Sample Execution results — Using string_AGG in SQL Server 2017

With SQL Server 2017 release, there is a better way to do this using string_agg built-in function. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator.

STRING_AGG ( input_string, separator ) [ order_clause ]

input_string - is the column name of any type that can convert to a comma separated string
separator - is the separator caharacter like , or ; etc
[ order_clause ] - specifies the sort order of concatenated results using WITHIN GROUP clause

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )
Please note that, The STRING_AGG() ignores NULL and it does not add the separator for NULL when performing concatenation.

Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 

Select SessionID, STRING_AGG(CourseID,',') From BuildCommaSeparatedString Group by SessionID

--To build the comma separated string in an order way
Select SessionID, STRING_AGG(CourseID,',') within group (Order by CourseID asc) 
From BuildCommaSeparatedString Group by SessionID

Sample Execution results A quick look on Performance benefit

The below snapshot clearly indicates that the performance benefit of string_AGG built-in function over XML PATH approach.


If you are in SQL Server 2017 – use built-in function string_AGG instead of any other method. I have seen this outperform many times than a custom built code. If you do not agree or have any different experience, please share the specific case with an example for the benefits of readers. Thanks in advance!

Python in SQL Server

Python is an interpreted programming language. Python is a very strong programming language for multi purposes like machine learning/complex computing/ scientific and theoretical based solutions etc. I do not want to cover the details as We have a “Stairway to Python” initiative by Kiran Reddy. I strongly recommend to read the series to know and learn more about Python. For now, Let us cover Python in SQL Server…


Python support in SQL Server, is a great move from Microsoft to challenge other technologies.SQL Server 2017 by default has in-built Python libraries to support the requirements.

Let us quickly see the prerequisites for Python in SQL Server.

1. Install SQL Server 2017 Developer or above edition

As SQL Server 2017 has introduced support of Python script/code execution, Lets us download and install SQL Server 2017 Developer version as a first step.

2. Select the Machine learning features that includes Python and R while installing SQL Server 2017

It is important for us to understand we need to select Machine Learning Services(previously known as R Services(SQL 2016) in “Feature Selection” while installing SQL Server 2017.

3. Make sure the Launchpad Service is selected with Automatic start-up

Launchpad is an integral part of Machine Learning Services execution meaning to executing the Python or R code in SQL Server. The below diagram explains the usage of Launchpad service.

Note, This is important when you have multiple Launchpad services exists in the same environment and one of them have any issues that could not start, none of the Launchpad services may not start at all(manually). To be safer side, we can make this setting Automatic at initial stage.

4. Enable “External Scripts Enabled” in the configuration

To run Python code in SQL Server, we need to use sp_execute_external_script system stored procedure. To use this procedure, we need to enable the configuration option “external scripts enabled” as below.

sp_configure 'external scripts enabled',1
reconfigure with override

5. Restart the SQL Service

This is important as “External Scripts Enabled” option is not a dynamic configuration that needs an explicit restart of the service to affect the changes.

A quick Demo

Now, we have set up the environment Lets quickly do a script execution.

EXEC sp_execute_external_script @language = N'Python', 
@script = N'print("Python in SQL Server is a revolution!!!")'

Thats it for now, hope you enjoyed the post.