Why we use CLR procedures

Question

Why we use CLR procedures. Is there any significance of CLR Procedures or any example where CLR Procedure is the only solution?

Solution 1

Imagine you want to validate some of your data fields in SQL Server using a regular expression. To this day, even in SQL Server 2008 R2, this is virtually impossible with just T-SQL code.

However, with a little help from a CLR stored procedure or stored function, this would be a piece of cake.

T-SQL is very strong when it comes to manipulating sets of data - use it for that.

CLR is very strong in other areas, like string and date manipulation, calling external services (WCF, web services).

So T-SQL stored procedures and CLR stored procedures are a nice complement - each solving a specific set of challenges that the other is not particularly good at.

Solution 2

There are a few things that cannot be done in SQL Server (or that are not done as well as in managed code, in some cases).

• The CLR has RegEx.
• You can call web services.
• The CLR has better performance (if you had to do a lot of math on every row, for example)
• Code reuse
• Write in the language you're used to (VB.Net, C#, etc).

Solution 3

I posted the following answer to a similar question: Advantage of SQL SERVER CLR. I will add here though, given that something was mentioned in at least 2 answers: C# / VB.net / etc being a language someone is more comfortable with than T-SQL should not be a reason to use SQLCLR over T-SQL. If someone doesn't know how to accomplish something in T-SQL, first ask for help in finding a T-SQL solution. If one does not exist, then go the CLR route.

---

SQLCLR / CLR Integration within SQL Server is just another tool to help solve certain (not all) problems. There are a few things that it does better than what can be done in pure T-SQL, and there are some things that can only be done via SQLCLR. I wrote an article for SQL Server Central, Stairway to SQLCLR Level 1: What is SQLCLR? (free registration is required to read articles there), that addresses this question. The basics are (see the linked article for details):

• Streaming Table-Valued Functions (sTVF)
• Dynamic SQL (within Functions and Triggers--can access inserted and deleted tables)
• Better Access to External Resources / Replace xp_cmdshell
  • Passing data in is easier
  • Getting multiple columns of a result set back is easier
  • No external dependencies (e.g. 7zip.exe)
  • Better security via Impersonation
• Ability to Multi-thread
• Error Handling (within Functions)
• Custom Aggregates
• Custom Types
• Modify State (within a Function and without OPENQUERY / OPENROWSET)
• Execute a Stored Procedure (read-only; within a Function and without OPENQUERY / OPENROWSET)
• Performance (note: this is not meaning in all cases, but definitely in some cases depending on the type and complexity of the operation)
  • computations / string-manipulation
  • SQLCLR UDFs, if marked with DataAccess = None, can be used in parallel execution plans, whereas T-SQL UDFs prevent parallel plans
• Can capture output (i.e. what is sent to the Messages tab in SSMS) (e.g. PRINT and RAISERROR with a severity = 0 to 10) -- I forgot to mention this one in the article ;-).

One other thing to consider is, sometimes it is beneficial to be able to share code between the app and the DB so that the DB has insight into certain business logic without having to build custom, internal-only screens just to access that app code. For example, I have worked on a system that imported data files from customers and use a custom hash of most of the fields and saved that value to the row in the DB. This allowed for easily skipping rows when importing their data again as the app would hash the values from the input file and compare to the hash value stored on the row. If they were the same then we knew instantly that none of the fields had changed so we went onto the next row, and it was a simple INT comparison. But that algorithm for doing the hash was only in the app code so whether for debugging a customer case or looking for ways to offload some processing to back-end services by flagging rows that had at least one field with changes (changes coming from our app as opposed to looking for changes within a newer import file), there was nothing I could do. That would have been a great opportunity to have a rather simple bit of business logic in the DB, even if not for normal processing; having what amounts to an encoded value in the DB with no ability to understand its meaning makes it much hard to solve problems.

If interested in seeing some of these capabilities in action without having to write any code, the Free version of SQL# (of which I am the author) has RegEx functions, custom Aggregates (UDAs), custom Types (UDTs), etc.