2

I want to use memory optimization/natively compiled stored procedure features to benefit from performance improvements. So, I guess I have to upgrade SQL Server 2012 to SQL Server 2016. Ideally I would like to reuse some of the T-SQL code.

I have migrated some code but previously I was relying heavily on using GROUP_CONCAT() function to aggregate strings which is a custom CLR aggregate function. It basically concats strings (delimited by comma) when using GROUP BY, like follows;

SELECT City, dbo.GROUP_CONCAT(FirstName) AS Names
FROM Persons
GROUP BY City

City     Names
London Mark,Scott,Rob
Paris   Francois,Liza

But since CLR functions can't be used with natively compiled stored procedure, can anyone suggest some way of achieving the desired output.

I am aware of FOR XML PATH approach to get similar results, but that too is not supported in natively compiled stored procedure.

Also, please note that, I have places where multiple columns are specified in the GROUP BY clause.

sql_xc
  • 21
  • 1
  • If you can further upgrade to the next version of SQL Server (currently in beta stage, unfortunately) you can use the new function [STRING_AGG](https://msdn.microsoft.com/en-us/library/mt790580.aspx) which is a built-in string concatenation aggregate function. Since it's an internal function, it should work here. – Alejandro Mar 10 '17 at 12:36
  • I think you should check this answer out: http://stackoverflow.com/a/545672/6492765 – MK_ Mar 10 '17 at 13:06

0 Answers0