To check the performance of the fn_workdays() UDF, I provided a range of more than half a million days:
SELECT dbo.fn_workdays
('20000101', '39991231')
This function ran for 4 seconds on my laptop. Usually, you'd invoke the function with much smaller ranges, but providing a large range is somewhat similar to invoking the function with smaller ranges many times. Let's look at an approach that gives better performance.
The Set-Based Approach
Using a set-based approach to solving the working-days problem means writing a query against a table. A common practice among T-SQL programmers is to create an auxiliary Calendar table that stores all possible dates within a certain range and that has a column that specifies the type of day. Usually, such a column stores a certain value (such as w) for a working day and another value (such as h) for a non-working day. In addition, you might want to be able to track different day properties (e.g., weekend, annual holiday, other holiday, strike). If you want to express many combinations of day properties (e.g., weekend plus annual holiday, weekend plus other holiday, non-weekend, strike), you can have the day_type column store a bitmap in which each bit represents a different day property. The smallint data type is sufficient for up to eight types, which is usually more than enough. Here's the code to create a sample Calendar table:
CREATE TABLE Calendar
(
cday datetime NOT NULL PRIMARY KEY,
-- bit 0(1)=weekend,
-- bit 1(2)=annual holiday,
-- bit 2(4)=other holiday,
-- bit 3(8)=strike
day_type tinyint NOT NULL
)
Prev. page
1
[2]
3
4
5
next page 
