Some of the benefits of using CLR procedures are:
Granular control:
SQL Server administrators have little control over XPs. A SQL Server
administrator can assign one of SAFE, EXTERNAL_ACCESS, or UNSAFE permissions to
exert varying degrees of control over the operations that managed code is
allowed to perform Using the Code Access Security model.
Reliability:
Managed code in the SAFE and EXTERNAL_ACCESS permission sets, provides a
reliable programming model
Data access:
Managed CLR code can access local data using a more natural and efficient
programming model that takes advantage of the current connection and
transaction context.
Additional Data Types:
The managed APIs support new data types introduced in SQL Server 2005, while the
ODS APIs have not been extended to support these new types.
Scalability:
With CLR code, SQL Server can detect that a given thread has not yielded for a
long period of time and force the task to yield so that other work can be
scheduled.
