chris.fauvel (6/30/2010)
Bradley Deem (6/29/2010)
I recommend using a CLR for this. Microsoft has a great example here http://msdn.microsoft.com/en-us/library/ms131056.aspx. I'm curious what the performance differences are. Admittedly, I have not compared the two because I've never had the CLR function not perform adequately.You can then use this as an aggregate, for example.
SELECT dbo.List(myColumn)FROM myTable
GROUP BY SomeOtherColumn
What version of SQLSERVER are you running with CLR? Any issues with the server crashing? Could the server crash if the CLR has an unhandled exception?
I see the power of CLR, but the boss and I are scared for the database.
Thanks
Chris
For this task you could build a SQL CLR assembly using the strictest permission set (SAFE), which indicates all managed code and no access to any external resources. It's highly unlikely you could write an assembly that would corrupt the memory space with SAFE permissions, but even if you could it would be limited to just the managed memory space and wouldn't bring down the server.
There are some things to know about using SQL CLR:
* SQL Server caches SQL CLR assemblies after it loads them the first time, for efficiency. In the case of memory pressure, SQL CLR assemblies are one of the first things to get unloaded. If you are on a machine with a lot of memory pressure SQL Server will need to reload your assemblies every time, adding overhead and cutting into any efficiency gains from using SQL CLR.
* When you're doing string concatenation operations like this you probably want to use an efficient .NET StringBuilder object to maximize efficiency. Normal string concatenation is notoriously slow because strings are immutable (a copy of the string is made everytime it is modified).
* SQL Server can't accurately cost a SQL CLR assembly in a query plan since it has no idea what you're doing in there.
* In some cases SQL CLR can prevent parallelization in query plans. When you pass in LOB (varchar(max), etc.) parameters to it, for instance. That may not be an issue in this case, but is something to be aware of.
* You need to explicitly handle NULLs on the .NET side when you use SQL CLR. One of the biggest mistakes I've seen is SQL CLR code that pretends NULLs don't exist. That usually results in hard-to-troubleshoot exceptions from the .NET side.
If you keep these things in mind, you shouldn't have any problems with SQL CLR. This particular exercise would actually make a very nice introduction to SQL CLR.
Mike C