Microsoft® SQL Server 2012 Unleashed

Deciding Between Using T-SQL or CLR Functions

One question that often comes up regarding user-defined functions is whether it’s better to develop functions in T-SQL or in the CLR. The answer really depends on the situation and what the function will be doing.

The general rule of thumb is that if the function will be performing data access or large set-oriented operations with little or no complex procedural logic, it’s better to create that function in T-SQL to get the best performance. The reason is that T-SQL works more closely with the data and doesn’t require multiple transitions between the CLR and SQL Server engine.

However, most benchmarks have shown that the CLR performs better than T-SQL for functions that require a high level of computation ...