Database Reference
In-Depth Information
As you see, each technology—T-SQL and CLR—has its own strengths and weaknesses. CLR is better at handling
imperative code and complex calculations, and it has a much lower invocation cost for user-defined functions. T-SQL,
on the other hand, outperforms CLR in the data access area with exception of row-by-row processing, where .Net
SqlDataReader
class is faster than T-SQL cursors.
Summary
CLR code adds flexibility to SQL Server. It helps improve performance of functions that require complex calculations
and expands the standard function library by adding new methods. It lets you access external resources from within
the database code.
CLR code, however, comes at a performance and security cost. CLR code runs within the SQL Server process,
which adds an extra load and can introduce significant performance issues when coded incorrectly. Moreover, CLR
introduces security challenges. It needs to be enabled on the server level, which violates security best practices. It also
breaks ownership chaining, which will require using special care with permissions.
Keeping all of this in mind, you should always evaluate other options before using CLR code. You need to
consider moving imperative logic to the client application and/or refactor your queries to use declarative set-based
logic whenever possible.
As with the other technologies available within SQL Server, the question: “What is better: T-SQL or CLR?” has no
right answer. Different use-cases require different solutions, and it is always beneficial to evaluate and test all of the
available options during the decision-making stage.
