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)
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])
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,