SQL Server CLR stores procedures in data processing tasks - good or evil?

Question

In short, is this a good design decision to implement most of the business logic in CLR stored procedures ?

I read a lot about them recently, but I can’t understand when they should be used, what are the best practices, are they good enough or not.

For example, my business application should

• parse a large text file with a fixed length,
• extract some numbers from each line in the file,
• according to these numbers, some complex business rules apply (including matching regular expressions, matching patterns with data from many tables in the database, etc.),
• and as a result of this calculation, the records in the database are updated.

There is also a graphical user interface for selecting a file, viewing results, etc.

This application seems to be a good candidate for implementing the classic three-tier architecture: data layer, logic level and GUI layer.

• Data tier will access the database
• Logic Layer will act as a WCF service and implement business rules by interacting with the data layer
• The GUI layer will be the means of communication between the logical layer and the user.

Now, having thought about this project, I see that most business rules can be implemented in the SQL CLR and stored in SQL Server. I could store all my source data in a database, start processing there and get the results. I see some advantages and disadvantages of this solution:

Pros

• Business logic is approaching data, which means less network traffic.
• Process all data at once, possibly using concurrency and an optimal execution plan.

against

• The scattering of business logic: some part is here, some part is.
• A dubious design decision may run into unknown problems.
• It is difficult to implement a progress indicator for a processing task.

I would like to hear all your opinions on SQL CLR. Does anyone use it in production? Are there any problems with this design? It's good?

Answer 1

I don’t do this - CLR ins SQL Server is great for many things (calculating hashes, manipulating strings that SQL just sucks in, a regular expression for checking field values, etc.), but complex logic, IMHO, has no business in Database.

This is one of the performance issues, and it is VERY expensive to scale. In addition, either I'm all there, or ... well, I have a serious service problem.

Answer 2

Personally, I prefer to have business functionality that is independent of the database. I use only CLR stored procedures when I need an advanced data query (to create a format that is not easy to do in SQL). Depending on what you do, I usually get the best performance results with standard stored procedures, so I personally use them only for my advanced tasks.

My two cents.

NTN.

Answer 3

Generally, you probably do not want to do this if you cannot get a significant performance advantage, or if there is a compelling technical reason for this. An example of such a reason could be a user aggregate function.

Some good reasons to use CLR stored procedures:

• You can take advantage of unique technology, such as a custom aggregation function.

• You can benefit from the performance of CLR Sproc - perhaps for quick write-by-write processing, where you can read using the fast forward cursor, buffer output in the kernel and load it into the destination table in batches.

• You want to wrap some .Net code or a .Net library and make it available for SQL code running on the database server. An example of this might be matching regular expressions from an OP question.

• You want to trick and wrap something uncontrollable and terribly unsafe to make it accessible from SQL code without using XP. Technically, Microsoft said XP was out of date, and many installations turned them off for security reasons.
  
  From time to time, you do not have the opportunity to change the code on the client side (perhaps the application), so you may need to initiate external actions from the database. In this case, you may need the trigger or stored procedure to interact with the outside world, perhaps by requesting the status of the workflow, writing something to the file system, or (in more detail) conducting the transaction to the remote mainframe system via the scraper library screen.

Bad reasons for using CLR stored procedures:

• Minor performance improvements for something that is usually done at an intermediate level. Please note that disk traffic is likely to be much slower than network traffic if you are not going to transfer huge amounts of data over a network connection.

• CLR sprocs are cool and you want to put them on your CV

• Unable to write set-oriented SQL.