CODEWITHSUNDEEP
sqlsql-serversql-server-2012sqlclr
Ragul
Ragul

Reputation: 512

When do we need a CLR function in SQL Server?

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 !

Upvotes: 2

Views: 4931

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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

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):

Upvotes: 11

Related Questions