Gary Varga,

In my experience I use the following as a best practice:

•Validate all input parameters and throw the appropriate exception when the parameter is not correct

•Use error handling and re-throw exceptions to the caller. Do not try and log exception in the CLR - have the T-SQL (or caller) do that.

•Write multiple smaller precise CLR methods verses one big one that does everything. This way you could take advantage of both worlds (T-SQL for set operations and CLR for everything else). I have also found that performance degrades on larger CLR objects. Using debuggers, it appears that SQL Server tries to determine how to perform a ROLLBACK on all CLR calls. The larger the CLR object, the longer it takes before the code is actually executed.

•When writing aggregations, build the result as the aggregation moves through the group verses storing all group members than performing the action. This will improve performance and reduce the required cache on large groups.

I'm sure there are more, but those are my big ones.

Tom