It is well known that nested calls of scalar UDFs are a huge drag on performance, but in many cases inline UDFs can be nested without performance penalty. Here is a simple repro that you can run and see for yourself. Suppose that you already have two inline UDFs: one returning the previous calendar day and another returning the first day of the month, as follows:
CREATE FUNCTION Readers.GetFirstDayOfMonth(@d DATETIME)
RETURNS TABLE AS RETURN(
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101') AS FirstDayOfMonth
)
GO
DROP FUNCTION Readers.GetPreviousDay
CREATE FUNCTION Readers.GetPreviousDay(@d DATETIME)
RETURNS TABLE AS RETURN (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', @d) - 1, '19900101') AS PreviousDay
)
GO
Suppose that you need to develop a UDF returning the last day of the previous month. You can develop it from scratch:
CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Merged(@d DATETIME)
RETURNS TABLE AS RETURN(
SELECT DATEADD(DAY, DATEDIFF(DAY, '19900101', DATEADD(MONTH, DATEDIFF(MONTH, '19900101', @d), '19900101')) - 1, '19900101')
AS LastDayOfPreviousMonth
)
Alternatively you can reuse your two existing UDFs in several ways, as follows:
CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested1(@d DATETIME)
RETURNS TABLE AS RETURN(
SELECT p.PreviousDay AS LastDayOfPreviousMonth
FROM Readers.GetFirstDayOfMonth(@d) AS f
CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonth) AS p
)
GO
CREATE FUNCTION Readers.GetLastDayOfPreviousMonth_Nested2(@d DATETIME)
RETURNS TABLE AS RETURN(
SELECT p.PreviousDay AS LastDayOfPreviousMonth
FROM (SELECT FirstDayOfMonth FROM Readers.GetFirstDayOfMonth(@d) AS f) AS f
CROSS APPLY Readers.GetPreviousDay(f.FirstDayOfMonth) AS p
)
To benchmark, I used my Data.Numbers table whihc has 1 million rows. You can use any large enough table.
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
-- this table has 1 million rows
SELECT COUNT(*) FROM Data.Numbers
DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Merged(@d);
GO
DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested1(@d);
GO
DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=LastDayOfPreviousMonth --INTO #t1
FROM Data.Numbers
CROSS APPLY Readers.GetLastDayOfPreviousMonth_Nested2(@d);
GO
-- this measures the overhead of benchmarking,
-- scanning the table and assigning a value to a variable
DECLARE @d DATETIME, @d1 DATETIME;
SET @d = '20090512';
SELECT @d1=@d --INTO #t1
FROM Data.Numbers
I did not notice any difference in performance beyond usual noise. Of course, in some very complex cases performance of nested inline UDFs might suffer, but in general this is not the case and usually you can reuse your inline UDFs without performance penalty.
My previous post about inline UDFs is this:
Reuse Your Code with Table-Valued UDFs