SQL Server 2005 Beta 2 Developer Edition is available for download at MSDN Subscriber downloads. Go fetch ;)

I’ve seen a lot of posts about the CLR Hosting support in SQL Server 2005, and quite a few of them discuss the possibility of moving business code into the SQL Server engine. I think it is time to establish some balance here, and I’m going to throw in my 2 cents.

CLR Hosting has a few obvious use cases, but it is in no way a replacement for T-SQL. If you are writing extended stored procedures then this is definitely the only logical way to go. It has a much better and safer programming model than the native one. If you are writing complex algorithms that can severely limit your result set then it is probably a good idea to put that into the server as well. T-SQL stored procedures that have massive amounts a non-dataset related code like encryption, conversions and extensive string manipulations could probably benefit from being completely or partially turned into managed CLR functions.

If you on the other hand are writing classical dataset manipulation and selection procedures, then T-SQL is a language that is highly optimized and specifically designed for just that purpose. You should keep in mind that managed stored procedures still use T-SQL to interact with the relational database engine; look at some code samples!

If you take a step back, you will see that you are making a decision about when it makes sense to utilize the database server processor over the application server processor. Clearly, application servers are a lot cheaper and usually a lot easier to scale out. At the end of the day, in any well designed distributed architecture, the database server is going to be your bottleneck. It would probably make sense to keep whatever processing you can away from that precious resource.

However, if you are using an algorithm to determine what records to return to the client, and you expect that it may severely limit the amount of records returned to the client, then it probably makes sense to put it on the database server. Returning 2GB of data to the application server, and then filtering away 98% before returning it to the client may be a massive waste of resources. You’ll have to make an informed tradeoff decision.

There are no absolute rules, but you will need to evaluate every single case for yourself. My advice is to stick with the way you’ve been writing applications with SQL Server 2000 and keep the T-SQL stored procedures the way they are. At least then you will know that whenever you utilize managed code in the SQL Server, you’ve made a conscious tradeoff rather then blindly following the ever popular anti T-SQL movement. Regarding business logic, keep it on your application tier where it has been living so happily over the last few years. Once again, if you do decide to move it to the SQL Server make sure it you’ve made a well informed decision that works with both your application and your business requirements.

Leave your defaults the way they are; it’s an evolution not a revolution.