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?

Answer 1

Well, if you want:

• to do complex operations on data, and
• want to be close to the data (i.e. not in ASP.NET or a Winforms app), and
• you'd prefer to write the code in C# than SQL.

That is the case when you use a CLR procedure. I don't have an example off the top of my head, but it should be possible to imagine one.

-- Edit:

An imagined one could be the conversion of the data into a datawarehouse type structure. In this case, you may wish to reformat and run a bit of analysis. It may be suitable to do it in a CLR then. (I'm not suggsting this is exactly what I'd do, but it could be considered).

Answer 2

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.

Answer 3

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

Answer 4

If you would like to do some non-trivial math calculations, or call external web services or things like that, you cannot do them from T-SQL, and a .Net stored procedure or function would be really helpful in solving these.

You can also write aggregate functions with CLR procedures, which could not be done in T-SQL.

Of course, for data manipulation, T-SQL stored procedures would perform better, and are easier to write.