Archive for October, 2009

These days I had a customer requesting to calculate working time difference between two dates. Ok nothing strange, apart from the fact that the idea of working time can be very dynamic. I mean, one should take into consideration a lot of things:

  • Lunch pause (normally working time goes between 08->12, 14->18)… for instance.
  • Partial-working days (maybe one works only half a day on saturdays, for instance)
  • Holidays

OK now try to build a T-SQL function that quickly calculates the minutes difference using the above parameters (without taking a lot of time… obviously)… and the answer will be easy:

  • T-SQL is not the best approach for this kind of algorithms
  • It takes a lof of development and optimization time

So, what about experiencing other ways?

Why don’t try to create or use an existing .NET function by leveraging the power of .NET directly on SQL-Server statements?

Take this example:

select DateDiff(minute, FromDate, ToDate) as Minutes from Activities

We would like to make something like:

select dbo.WorkingtimeDiff(FromDate, ToDate) as WorkingMinutes from Activities

Ok, in order to do that, we need:

  • Build our own .NET assembly using VStudio 2005/2008/2010 on Framework 2.0
  • Publish our assembly to SQL Server database
  • Enable SQL Server “clr enabled” option:
  • Create a T-SQL compatible mapping for your Class/Method on the Published assembly (CREATE FUNCTION … EXTERNAL NAME [asm].[class_fullname].[method])

That’s it.

Obviously don’t think about replacing any T-SQL function with this logic. As per all the other techniques, this one is valid for certain kind of calculations. Wokring time calculation is one of them. But if you want to perform a plan-injection to the main query plan, then this is not the right way to go. Think about replacing somem multi-statement functions with this logic when the logic behind the calculation is simpler and faster to execute when done on .NET.

Hope this helps,



Read Full Post »

These days I need to start developing a new workflow engine for all our company applications and I am going to develop them using WorkFlow Foundation 4.0, which now is on Beta 2 along with VStudio 2010.

I have to say that I am quite excited as they say the developer environment loads very quickly if compared to Beta 1, which was quite slow.

I guess that some debug code has been removed 🙂

I hope to give some feedbacks on first uses in the next weeks.

Hope also to have some answers in this Beta version about designer rehosting expression editors, since I need to build my own and no documentation is available. I am thinking about reverse engineering the classes to know how to build my own, but I know also that this is a waste of time, that’s why I waited since now.

Hope to have some good hands-on news quickly!

Read Full Post »