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

Question

In short - is it a good design solution to implement most of the business logic in CLR stored procedures?

I have read much about them recently but I can't figure out when they should be used, what are the best practices, are they good enough or not.

For example, my business application needs to

• parse a large fixed-length text file,
• extract some numbers from each line in the file,
• according to these numbers apply some complex business rules (involving regex matching, pattern matching against data from many tables in the database and such),
• and as a result of this calculation update records in the database.

There is also a GUI for the user to select the file, view the results, etc.

This application seems to be a good candidate to implement the classic 3-tier architecture: the Data Layer, the Logic Layer, and the GUI layer.

• The Data Layer would access the database
• The Logic Layer would run as a WCF service and implement the business rules, interacting with the Data Layer
• The GUI Layer would be a means of communication between the Logic Layer and the User.

Now, thinking of this design, I can see that most of the business rules may be implemented in a SQL CLR and stored in SQL Server. I might store all my raw data in the database, run the processing there, and get the results. I see some advantages and disadvantages of this solution:

Pros:

• The business logic runs close to the data, meaning less network traffic.
• Process all data at once, possibly utilizing parallelizm and optimal execution plan.

Cons:

• Scattering of the business logic: some part is here, some part is there.
• Questionable design solution, may encounter unknown problems.
• Difficult to implement a progress indicator for the processing task.

I would like to hear all your opinions about SQL CLR. Does anybody use it in production? Are there any problems with such design? Is it a good thing?

Answer 1

I do not do it - CLR ins SQL Server is great for many things (calculating hashes, do string manipulation that SQL just sucks in, regex to validate field values etc.), but complex logic, IMHO, has no business in the database.

It is a single point of performance problems and also VERY expensive to scale up. Plus, either I put it all in there, or - well - I have a serious problem maintenance wise.

Answer 2

Personally I prefer to have business functionality not dependent on the database. I only use CLR stored procedures when I need advanced data querying (to produce a format that is not easy to do in SQL). Depending on what you are doing, I tend to get better performance results with standard stored procs anyway, so I personally only use them for my advanced tasks.

My two cents.

HTH.