What are the limitations of CLR Assemblies for SQL Server 2008?

I am planning some reports with quite a heavy load of calculations, which I thought is better to transfer to a custom .NET assembly loaded to Microsoft SQL Server. The companies who will use this will only use SQL Server Enterprise editions, so no problem with feature support.

The question is:

Is it actually a good idea?

I want to export this functionality because I want to be able to use features like:

• Multi-threading (the number of threads will be the minimum b开发者_运维知识库etween the processed entities, and the maximum number set in configuration file. I don't know any other upper limit I should specify.)
• Unmanaged code (C++ libraries for stream processing)
• Sometimes even COM Interop or shell commands, though this is less likely.

Will they work fine? Are there any limitations I should know about, in my case?

Everything you list is possible with some limitations. The Host Protection Attributes allow access to create threads, but prohibit access to Thread.Join etc. Read more about it on msdn

Now the question is "Should you do this"? I think the approach is not very sound as it will put a big processing load on your database server which will be very hard to scale if needed. I think a better approach is to add a Custom assembly to SQL Server Reporting Services and let the processing happen over there. If you run into scalability issues an additional reporting services machine can be added.

There are also no restrictions on methods & classes in assemblies loaded in Reporting Services.