Best Practices for CLR Assemblies vs. TSQL

A very good article on MSDN will help developers determine when to use the CLR stored procedures, functions and triggers versus traditional TSQL ones. The guidance is as follows directly from the article:

Use declarative T-SQL SELECT, INSERT, UPDATE, and DELETE statements whenever possible. Procedural and row-based processing should be used only when the logic is not expressible using the declarative language.

If the procedure is simply a wrapper for declarative T-SQL commands it should be written in T-SQL.

If the procedure primarily involves forward-only, read-only row navigation through a result set with some processing of each row, using the CLR is likely more efficient.

If the procedure involves both significant data access and computation, consider separating the procedural code into a CLR portion that calls into a T-SQL procedure to perform data access, or a T-SQL procedure that calls into the CLR to perform computation. Another alternative is to use a single T-SQL batch that includes a set of queries that are executed once from managed code to reduce the number of round trips of submitting T-SQL statements from managed.

Essentially, if you need to do row by row computations, you should use the CLR. If you need to do large data set manipulation (i.e., joinng tables, derrived tables or relational tasks) use TSQL. CLR objects are better at crunching computations for each row while TSQL is better from a set-based perspective.

Check out the article!