ASP.NET, SQL Server, WPF, C#, VB.NET

IsInvariantToDuplicates is a performance *hint* to the query engine that it  
  
doesn't have to call more than once with the same value. It doesn't  
  
*prevent* the engine from calling,  if you only have a few cities, if the  
  
selectivity/cardinality isn't enough. You'll have to check yourself that the  
  
city already isn't in the aggregated list (use one of the .NET  
  
string-handling functions), if the engine decides to call.  
  
For example, if you're using an aggregate like MIN, and you have 1000 rows,  
  
each with the value "1", the engine can create an optimized plan. It can  
  
also decide to call MIN once for each row.  
  
Also, with an aggregate that does string list processing, be careful that  
  
the list doesn't exceed 8000 bytes/4000 Unicode characters - 2 bytes  
  
overhead.  
  
Cheers,  
  
Bob Beauchemin  
  
http://www.SQLskills.com/blogs/bobb  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:eAxsopuKGHA.1076@TK2MSFTNGP10.phx.gbl...

Hi,  
  
I have written a UDA that concatenates cities of each country. For example:  
  
USE Northwind  
  
GO  
  
SELECT Country, dbo.MyUDA(City) AS Cities FROM Customers  
  
GROUP BY Country  
  
GO  
  
It produces a result like this:  
  
Country        Cities  
  
------            ------  
  
Brazil            Sao Paulo, Rio de Janeiro, Sao Paulo, Sao Paulo, Campinas  
  
France            Lyon, Lyon, Nantes, Lille, Nantes, Nantes  
  
But I don't want duplicate cities in the concatenated result. I thought  
  
IsInvariantToDuplicates property on my Accumulate method will correct the  
  
problem however it didn't. What does IsInvariantToDuplicates do and how can  
  
I remove duplicates?  
  
Thanks in advance,  
  
Leila

Hi Adam,  
  
I think bottom line is the same, writers of UDAggs must be prepared to  
  
accept duplicate values if IsInvariantToDuplicates = "True". I like your  
  
reasoning better, however. Without a "Cost" field on a UDAgg attribute it  
  
wouldn't be possible to figure out when/if its worth it to call less, but  
  
IsInvariantToDuplicates = "True" would allow intermediate duplicate values  
  
(call more). Calling more or less times would produce the wrong answer with  
  
SUM but not with MAX.  
  
Cheers,  
  
Bob Beauchemin  
  
http://www.SQLskills.com/blogs/bobb  
  
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message  
  
news:uypaKvzKGHA.1028@TK2MSFTNGP11.phx.gbl...

Hi Bob,  
  
I thought I'd read the opposite -- that IsInvariantToDuplicates is a  
  
performance hint when set to "True", as it gives the optimizer more options  
  
in terms of optimizing the query plan, due to the fact that some plans will  
  
produce intermediate duplicates.  Unfortunately, this isn't documented too  
  
well (really, at all) in MSDN...  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--  
  
"Bob Beauchemin" <no_bobb_spam@sqlskills.com> wrote in message  
  
news:%23Fg0BTzKGHA.2040@TK2MSFTNGP14.phx.gbl...

In addition to words of Bob and Adam: you can use  
  
SELECT dbo.MyUda(DISTINCT City).  
  
Try it, and please post the result.  
  
--  
  
Dejan Sarka, SQL Server MVP  
  
Mentor, www.SolidQualityLearning.com  
  
Anything written in this message represents solely the point of view of the  
  
sender.  
  
This message does not imply endorsement from Solid Quality Learning, and it  
  
does not represent the point of view of Solid Quality Learning or any other  
  
person, company or institution mentioned in this message  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:eAxsopuKGHA.1076@TK2MSFTNGP10.phx.gbl...

Wow! Thanks Dejan! It works fine :-)  
  
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in  
  
message news:%23oo%23GK7KGHA.360@TK2MSFTNGP12.phx.gbl...