Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at http://msmvps.com/blogs/robfarley



13 October 2005

Median aggregate

Surely there must be a way of making a median aggregate in SQL CLR. Dan Sullivan doesn't think there is. It's easy to write something that will do it in T-SQL, but this isn't a proper aggregate function, like ones you can make with SQL CLR.

So where are the issues?

Well, the MaxByteSize limit of 8k is a pain, because without it, you'd be able to store your whole list of values, and then sort them and find the middle in the Terminate function.

If CLR aggregates could access the database, then the list of values could be stored in a working table. But you can't do that either (plus it would be really inefficient).

I'm still not convinced it's impossible. One day I'll work it out.

Posted by Rob Farley at 6:05 pm