SQL Server : use function to calculate a new column

Question

I've created a function that calculates the amount of business days between two dates using a calendar table to exclude holidays and weekends.

 CREATE FUNCTION dbo.BusinessDays 
     (@sDate SMALLDATETIME, 
      @eDate SMALLDATETIME) 
 RETURNS TABLE 
 AS 
     RETURN (SELECT COUNT(*) dt 
             FROM dbo.Calendar 
             WHERE dt BETWEEN @sDate AND @eDate) 
Go

My question is: how do I use this function to compute a column from another table (dbo.HolidayRequestForm) which has two date columns?

I have tried

ALTER TABLE Test
    ADD HoursTaken AS dbo.BusinessDays ([StartDate], [FinishDate])

score 1 · Accepted Answer · answered Dec 20 '18 at 17:06

1

I think the best thing to do would be to refactor your function to return a scalar value:

CREATE FUNCTION dbo.BusinessDays
(
    @sDate SMALLDATETIME,
    @eDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @RTN INT;

    SELECT @RTN = COUNT(*) 
    FROM dbo.Calendar
    WHERE dt BETWEEN @sDate AND @eDate;

    RETURN @RTN;
END

Then your SQL to add the computed column remains the same.

If your function needs to remain as a table-valued function, you'll need to use CROSS APPLY:

SELECT * 
FROM dbo.HolidayRequestForm
    CROSS APPLY dbo.BusinessDays(StartDate, FinishDate) d

Which, as you can see, would make using a computed column quite tricky.

A couple of unrelated points:

I'm not sure if HoursTaken is the best name of your computed column, since it seems to be storing the number of days (not hours) between the 2 dates.
When using a date range in your query, it's advisable to use WHERE DateColumn >= @DateStart AND DateColumn < @EndDate. For more information, see this blog post.

answered Dec 20 '18 at 17:06

Zack

2,220
1
8
12

@GordonLinoff: I wasn't aware that SQL Server supported inline scalar functions (at least ones that pulled data from a table). Where could I find syntax/examples for that? – Zack Dec 20 '18 at 17:46
@zack - SQL Server does not support inline Scalar functions (except for in SQL 2019 -YAY!). T-SQL Inline Table Valued functions perform better for a whole host of reasons. Since you can't use Inline Table Valued functions for computed columns the OP could just use inline SQL; it will accomplish the objective without a crazy performance hit. – Alan Burstein Dec 20 '18 at 19:27
@Zack . . . That was some form of wishful thinking. – Gordon Linoff Dec 20 '18 at 19:45
@conor8630 - I think so. Haven't had a chance to play with 2019 yet – Alan Burstein Dec 22 '18 at 00:01

score 0 · Answer 2 · answered Dec 20 '18 at 19:51

You are right to want to use inline (pure) T-SQL functions. There's two kinds of T-sql functions in SQL Server: Inline Table Valued functions and Wrong. Unfortunately, you can't use Inline Table Valued functions for computed columns and, if performance is remotely important, you DO NOT want to use a T-SQL scalar UDF for a computed column.

The good news is, you don't need to. You have a couple options. First, you don't need a calendar table to calculate working days, you could use this logic for your computed column:

CASE WHEN SIGN(DATEDIFF(dd, @startDate, @endDate)) > -1 THEN
                (DATEDIFF(dd, @startDate, @endDate) + 1) --total days including weekends
               -(DATEDIFF(wk, @startDate, @endDate) * 2) --Subtact 2 days for each full weekend
    -- Subtract 1 when startDate is Sunday and Substract 1 when endDate is Sunday: 
    -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday'   THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, @endDate)   = 'Saturday' THEN 1 ELSE 0 END)

You could also use this logic to create an indexed view which is more flexible and could be a better option.

Here's a couple good links about why you NEVER use T-SQL scalar UDFs as computed columns, or constraints:

A Computed Column with a (scalar udf) might Impact Query Performance –Kun Cheng (SQLCAT)

Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints – Erik Darling (this article applies to computed columns too)

Another reason why scalar functions in computed columns is a bad idea – Erik Darling

Be careful with constraints calling UDFs – Tibor Karaszi

Beware-row-row-operations-udf-clothing – Brian Moran

Why does the Execution Plan include a scalar udf call for a persisted computed column? – Stack Overflow

I also need to remove holidays when finding the difference between two dates, so a calendar table was best fit for that purpose. — Conor8630, Dec 21 '18 at 08:07

SQL Server : use function to calculate a new column

2 Answers2