One of the most commonly asked questions I get from developers is, should this be done with a CLR function/procedure or in T-SQL? The addition of CLR in SQL Server brought a very powerful extension to the Database Engine. This article will focus on providing information on how to best make the decision to use CLR or T-SQL to solve a problem. The T-SQL language is based fundamentally on its declarative query language SQLECT/INSERT/UPDATE/DELETE, and much of the power of SQL lies here. There is also procedural language included to handle string functions, looping, and aggregations. This makes determining which method of development is best difficult for those new to the concept of CLR in SQL Server.
So just exactly how do you know when to use CLR over T=SQL? The answer really is not cut and distinct. A rule of thumb provided by Microsoft is to use "CLR-based progamming for logic that cannot be expressed declaritively in TSQL, and to complement the expressive power of the TSQL query language." The most common SQL Structure for CLR Implementations is the User Defined Function. Scalar value UDF's usually don't access the database data directly, the data is instead passed to the UDF as a parameter and a value is returned by the UDF.
In general, you should use T-SQL for SELECT/INSERT/UPDATE/DELETE operations as much as possible. You should not use CLR as a wrapper for these kinds of commands unless there is row-based processing that must occur where CLR will gain an advantage over the procedural language included in T-SQL. Complex mathematics, string parsing, accessing external datasources, and streaming results are all good areas to use CLR Integration. However, as new procedural logic like PIVOT and SQL XML are built into the procedural logic of T-SQL, it is important to understand what new features are available inside of T-SQL to prevent reinventing the wheel in CLR.
CLR provides access to a vast framework of prebuilt classes and routines for operations such as improved string-handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation, all of which are easily accessible from managed stored procedures, functions, triggers, and aggregates. It also allows for the definition of custom User Defined Types for modeling object type data such as geospatial data as a datatype in SQL Server. For operations that function on 3D data requiring x, y, and z axis data for a point in space, a UDT can be created to hold a point as a single piece of data while providing strong typing on that data. Additionally, CLR allows for Custom Aggregations by allowing the creation of a User Defined Aggregate, which can be used in non-indexed views and other declarative queries as if it were a built in aggregation.
However, there are certain cases where it would seem that CLR is the best answer to a problem, but due to the overhead of the context switch out to managed code, T-SQL can out perform the CLR. Simple string matches, and parsing can sometimes be done in T-SQL faster than in CLR. The only way to know is to test both methods and see which is better. If there is only a slight performance margin, measured in milliseconds only, then it may be best to stick to a T-SQL implementation to allow for a simplified coding model for the solution.
In the end the decision of when it is best to use a CLR implementation over a T-SQL implemenation depends on several factors including the composability requirements of the project, the performance gained by using CLR over T-SQL, whether or not there is a need to use external data sources, the type of operation being performed, and how the results need to be returned. As you get more familiar with using CLR in SQL Server, the decision will become easier to determine which method is best to solve a give problem.