.NET Managed Code in SQL Server
I just read an interesting DevX article that got me thinking about the new features in SQL Server 2005 that will allow managed code in stored procedures. A. Russell Jones discusses the pros and cons of what will certainly continue to be a hotly debated topic. His arguments center around what developers will demand and what development managers will want as well - they are not always the same thing.
I know that in my shop, what the developers “want“ rarely enters into the equation. It's all about what solves the business problem most effectively. As I've explained to many a pissed off developer over time, the best technology solution to a problem is rarely also the best business solution as well. If it were, we'd all be using Lotus Notes as our email and groupware solution, and we'd have moved to OS/2 back in the days when Windows 95 was still floundering around.
So, this certainly applies to the question of using .NET managed code in stored procedures as well. The issue is - what is going to solve the given business problem most effectively. T-SQL is pretty damn good at doing what it was intended to do, namely performing set-based CRUD operations on relational data. It's not particularly strong in looping or control-of-flow or calling outside libraries/methods/functions, but that's not what it was designed to be strong in. So if those things are essential to solving your business problem you may want to look hard at CLR-based stored procedures. If they're not, then there's nothing wrong with sticking to T-SQL, no matter how “cool“ or “fast” or “3l33t” the developers may think it would be to do otherwise.
Jones makes some unsubstantiated assertions about performance gains in using CLR-based procs over T-SQL. I'd like to see if there are any definitive tests that have been conducted, and under what circumstances. Properly tuned queries in T-SQL are pretty hard to beat, I would think. If anyone has done any testing or experimentation in this area, I'd love to hear about it.