Let’s say you have a table that records transactions along with the date of when the transaction took place. Wait, I’m being told that Production.TransactionHistory in AdventureWorks sample database matches this description. So let’s use that for this demonstration.

Here’s the simplified schema of that table.

CREATE TABLE [Production].[TransactionHistory]
 (
 [TransactionID] [INT] IDENTITY(100000, 1)
 NOT NULL ,
 [ProductID] [INT] NOT NULL ,
 [ReferenceOrderID] [INT] NOT NULL ,
 [ReferenceOrderLineID] [INT] NOT NULL ,
 [TransactionDate] [DATETIME] NOT NULL ,
 [TransactionType] [NCHAR](1) NOT NULL ,
 [Quantity] [INT] NOT NULL ,
 [ActualCost] [MONEY] NOT NULL ,
 [ModifiedDate] [DATETIME] NOT NULL
 );

Listing 1: Columns in the Production.TransactionHistory table

Some transactions are posted on holidays as well. I can tell by looking at the TransactionDate column.

Now, imagine you’ve a requirement to retrieve all rows from this table, and show next business day if the TransactionDate falls on a holiday.

Calendar Table

There’re several ways to achieve this, but I find using a calendar table to be the most efficient. Use the following script to create the calendar table.

CREATE TABLE dbo.Calendar
    (
      CalendarDate DATE ,
      WeekdayName VARCHAR(10) ,
      IsHoliday BIT
    );
GO

Listing 2: Create script of a simple Calendar table

This table contains every single date between a desired timeframe. IsHoliday column specifies whether the date is a holiday or not. Obviously you can add additional columns based on your needs.

I use the following script to populate the Calendar table with dates between 01/01/2000 and 12/31/2030.

 DECLARE @startdate DATE = '20000101' ,
@enddate DATE = '20301231';
WITH    c AS ( SELECT   Num = ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) - 1
FROM     sys.columns c
CROSS JOIN sys.columns c1
),
d AS ( SELECT   [date] = DATEADD(DAY, Num, @startdate) ,
Num
FROM     c
WHERE    Num >= 0
AND Num <= DATEDIFF(DAY, @startdate, @enddate)
)
INSERT dbo.Calendar
( CalendarDate ,
WeekdayName ,
IsHoliday
)
SELECT  [date] ,
[DayName] = DATENAME(WEEKDAY, [date]) ,
IsHoliday = CASE WHEN DATENAME(WEEKDAY, [date]) IN ( 'Saturday','Sunday' )
THEN 1
ELSE 0
END
FROM    d; 

Listing 3: Populate the Calendar table

Note that I’m specifying only Saturday and Sunday as the holidays since this is an example. I definitely don’t want to live in such a world. Obviously you’d have more holidays, use an Update statement to mark other holidays.

Baby Steps

09/01/2007 is marked as a holiday in this Calendar table. I can use the following query if I want to get the next business day.

 SELECT MIN(c.CalendarDate) AS NextBusinessDay
 FROM   dbo.Calendar AS c
 WHERE  c.CalendarDate >= '09/01/2007'
        AND c.IsHoliday = 0;

Listing 4: Find the next business day, a simple Select statement

Filter conditions CalendarDate >= ’09/01/2007′ and IsHoliday = 0 ensures that only business days greater or equal to the given date are returned from the table. Min() function is applied on this result set in the Select clause which returns the smallest – first in other words – date.

Table Valued Function and Cross Apply

I can convert the above Select statement to a table valued function as below.

 CREATE FUNCTION dbo.ufnGetNextBusinessDay
    (
      @TransactionDate DATE
    )
RETURNS @retNextBusinessDate TABLE
    (
      NextBusinessDate DATE
    )
AS
    BEGIN
        DECLARE @NextBusinessDate DATE;
        SELECT  @NextBusinessDate = MIN(c.CalendarDate)
        FROM    dbo.Calendar AS c
        WHERE   c.CalendarDate >= @TransactionDate
                AND c.IsHoliday = 0;
        INSERT  @retNextBusinessDate
                ( NextBusinessDate )
                SELECT  @NextBusinessDate;
        RETURN;
    END;
GO 

Listing 5: Table valued function to get next business day in SQL Server

This function accepts TransactionDate as an input parameter and returns a table with just one column (NextBusinessDate). The input parameter is passed to the Where clause replacing the static date 09/01/2007. This is the only change I made to the Select statement, all other lines of code are for the sake of the function syntax.

Lastly I write a Select statement and use Cross Apply to reference the table valued function as shown below.

 SELECT th.TransactionID ,
        th.ProductID ,
        th.TransactionDate ,
        th.Quantity ,
        th.ActualCost ,
        n.NextBusinessDate
 FROM   Production.TransactionHistory AS th
        CROSS APPLY dbo.ufnGetNextBusinessDay(TransactionDate) AS n;

Listing 6: Cross apply and table valued function to get next business day

Each TransactionDate from Production.TransactionHistory is passed to the function. The function returns the NextBusinessDate which is listed in the Select statement.

How Not To Do It

I frequently see people use a scalar user defined function in the Select statement as shown below (see line before From clause).

SELECT  th.TransactionID ,
        th.ProductID ,
        th.TransactionDate ,
        th.Quantity ,
        th.ActualCost ,
        dbo.ufnGetNextBusinessDay_scalar(th.TransactionDate)
FROM    Production.TransactionHistory AS th;

Listing 7: Don’t use scalar UDF is the select statement

This sure gives the same result as using table valued function and Cross Apply, however, scalar UDFs used in the Select statement are the worst in terms of performance. Why? Because scalar UDFs operate one row at a time. They’re just bad and ugly.

Want more proof? See for yourself below. Scalar function is used in the left solution and Table Valued function in the right solution. 199198 ms is 200 seconds and 2276 ms is 2 seconds. Just saying.