Navigation

Search

Categories

On this page

SQL Server 2005 Beta 2 at MSDN Subscriber Downloads
SQL Server 2005: CLR Hosting – Establishing Balance

Archive

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 83
This Year: 0
This Month: 0
This Week: 0
Comments: 20

Sign In

# Monday, 26 July 2004
SQL Server 2005 Beta 2 at MSDN Subscriber Downloads
Monday, 26 July 2004 15:48:38 UTC ( SQL Server 2005 )

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

Comments [0] | | # 
# Wednesday, 14 July 2004
SQL Server 2005: CLR Hosting – Establishing Balance
Wednesday, 14 July 2004 14:30:06 UTC ( Architecture | SQL Server 2005 )

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.

Comments [0] | | #