As a developer you may have or have not come across a situation where when designing your application you need to abstract it as far as possible in order to make it as configurable as possible thus making it applicable to a wide array of customers without tweaking the codebase to suit the individual customers. in such cases you end up with a situation where the application is brilliant but there is always the issue of reports in the very end...i hate reports but all systems i have built must have reports or the great workflows,the killer UI and configurability dont matter much to the user.
Reporting on heavly absracted systems is a pain a great pain...until i run into CLR Intergration...basically writting a stored procedure or function in .NET that returns your data in a Report friendly manner and utilizes your configurability.
Performance wise, its slow on the first run but it seems to match TSQL on subsequent calls.
i will not strive to write a tutorial on it here since you can find them all over the internet find some at http://www.devx.com/dbzone/Article/28412 and http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
learn it and get "Wows" when you present it on the next design meeting.
Cannot agree more with the DevX article. SQL Server CLR is pretty good for some situations but there are scenarios i've found where TSQL will ALWAYS be better, like as noted, set operations.
A good use of SQL CLR would be to expose the .NET regular expression engine to SQL server, given that the built in one is positively anaemic
I find writting some really tough algorithms in TSQL quite hard but since .NET is my default method of thought some of those have been sorted by CLR. I once did a google like search algorithm querying plain text for occurences of certain words and ranking them according to the number of times they occur.
I agree that TSQL is easy and effective since it is "closer to the metal" but using ADO.NET would push the functionality available in your applications to a new level.