When do we need a CLR function in SQL Server?

Question

I would like to know when does a CLR function need to be used in SQL Server.

Some says that CLR is mainly used to accomplish task which are not possible by T-SQL. But I can't figure out a good example for T-SQL failed to give me my exact output.

Can some one give me a exact example that can't be possible to accomplish through T-SQL ?

Samples will be much appreciated !

Answer 1

Asking when is SQLCLR needed is the wrong way of thinking about this. Like most other technologies, this is one way of accomplishing certain tasks and/or solving certain problems, but there are always work-arounds. The real question is when is using SQLCLR a good fit for solving a particular problem. And in that sense there is a list of things that are either much easier to do in SQLCLR, or that simply cannot be done in T-SQL.

I have published a fairly comprehensive list of SQLCLR capabilities in the following article Stairway to SQLCLR Level 1: What is SQLCLR? (free registration is required). Since registration, even free, is required for that site, I have copied that list below. For a more detailed explanation of each point and some additional info, please see that linked article.

Can Only Be Done In SQLCLR

• Streaming Table-Valued Functions (sTVF) (see article links at the end)
• Dynamic SQL in a Function (Scalar or TVF)
• Access External Resources / Replace xp_cmdshell:
  • Passing data in is easier
  • Getting multiple columns of a result set back is easier
  • No external dependencies
  • Can use Impersonation
• Multi-threading
• Error Handling in a Function (Scalar or TVF)
• Custom Aggregates (User-Defined Aggregates / UDAs) (see article links at the end)
• Custom Types (User-Defined Types / UDTs)
• Capture Messages (PRINT / RAISERROR(.., 10, 1) output)
• Participate in Parallel Plans (scalar functions)
• Intercept Result Sets
• Share / Cache Memory Across Sessions
• Set Persistent Environment Variables

Easier In SQLCLR

• Generate a GUID
• Generate a random number
• Modify State
• Execute a Stored Procedure (in a function -- scalar or TVF -- but it has to be read-only)

Performance

Comparing performance between T-SQL and CLR objects is a more complicated topic. To begin with, you can really only compare functionality that is the same between them. Then you have to consider the type of logic being done, is the logic being done efficiently in both types of code, if comparing functions is it being run via a SET statement or a multi-row query, and so on.

Microsoft has some guidelines (Performance of CLR Integration) as to what situations are better suited to CLR-based objects. If you will be working with SQLCLR objects, then you should at least be aware of that information. However, it all comes down to testing and this is all testable. I did some research on this topic in July of 2011 and published my findings here: https://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/.

EXAMPLES

There are many examples of what can be done, such as:

• Regular Expressions
• File System functions
• network / web / web-service related
• string splitting
• custom aggregates
• exporting data
• and so on...

For a more comprehensive list of things that can be done, check out the SQL# library (which I am the author of) that has a Free version that comes with the RegEx functions and many more. There is also a paid-for / Full version that has additional File System, Network, etc functions.

Additional examples in various articles on SQL Server Central (the site that requires free registration):

• Getting The Most Out of SQL Server 2005 UDTs and UDAs
• Exporting and Versioning Lookup Data: A Real-World Use of the CLR
• CLR Table-Valued Function Example with Full Streaming (STVF / TVF)
• Stairway to SQLCLR (series on SQLCLR) (examples in the articles in this series show how Impersonation works, calling a stored procedure from a function, etc.)