CLR concatenate function without duplicate values

Question

CLR concatenate function without duplicate values

krypto69

SSChampion

Points: 13604
More actions
January 8, 2016 at 12:23 pm

#310666

Hi,
I have a CLR that uses a concatenate function:
https://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/
(andy)
The problem is I am getting duplicates - I know it's supposed to return duplicates, and I know you can eliminate them via SQL side before sending to the CLR but is there a way to eliminate them within the CLR function?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

krypto69 (1/8/2016)
Hi,
I have a CLR that uses a concatenate function:
https://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/
(andy)
The problem is I am getting duplicates - I know it's supposed to return duplicates, and I know you can eliminate them via SQL side before sending to the CLR but is there a way to eliminate them within the CLR function?

Wouldn't you get a better answer if you asked the same question of the author that posted that article?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

I agree with Jeff on asking the author.

However, I also wonder why you want to filter out duplicates at the CLR side. Isn't it more efficient to filter them in the SQL, before even sending them over to the CLR function?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Hugo Kornelis (1/8/2016)
I agree with Jeff on asking the author.
However, I also wonder why you want to filter out duplicates at the CLR side. Isn't it more efficient to filter them in the SQL, before even sending them over to the CLR function?

I was going to ask the author that very question along with "Where's the performance comparisons against the T-SQL methods"? A lot of people that have written articles on SQLCLR methods get all knocky about their code so dunno if I'll bother.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

This group-concat SQLCLR aggregate generally outperforms the XML techniques available in T-SQL:

http://groupconcat.codeplex.com

The code on the site has test scripts comparing it to the XML techniques if you want to try it. Aaron Bertrand also did some test timings with it and found the SQLCLR edged out the XML PATH T-SQL but that is not even the slowest XML technique, XML PATH TYPE has that distinction:

http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

I like the tightness of the syntax available using the SQLCLR aggregate when compared to the XML techniques but admittedly I am a bit biased.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Thanks for the links, Orlando.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)