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

Question

I am planning several reports with a rather heavy load of calculations, which, it seemed to me, are better to transfer to a custom .NET assembly loaded on Microsoft SQL Server. Companies that will use this will only use SQL Server Enterprise editions, so there is no problem with feature support.

Question:
Is this a really good idea? I want to export this functionality because I want to be able to use functions such as:

• Multithreading (the number of threads will be the minimum between the processed objects and the maximum number set in the configuration file. I do not know any other upper limit that I have to specify.)
• Unmanaged code (C ++ libraries for stream processing)
• Sometimes even COM Interop commands or shells, although this is less likely.

Will they work fine? Are there any restrictions I should be aware of in my case?

Answer 1

Everything you list is possible with some restrictions. Host security attributes allow you to create threads, but deny access to Thread.Join, etc. Read more about this in msdn.

Now the question is: “Should you do this?” I think this approach doesn’t sound very good, as it will load a lot of load on the database server, which will be very difficult to scale if necessary. I think the best approach is to add a custom assembly to SQL Server Reporting Services and allow processing there. If you run into scalability issues, you can add an additional reporting services engine.

There are also no restrictions on methods and classes in assemblies that are loaded into Reporting Services.