SQL Server 2019: What Kind Of Scalar Functions Can’t Be Inlined?

Dating Sucks


There’s a lot of excitement (alright, maybe I’m sort of in a bubble with these things) about SQL Server 2019 being able to inline most scalar UDFs.

But there’s a sort of weird catch with them. It’s documented, but still.

If you use GETDATE in the function, it can’t be inlined.

Say What?


Let’s look at three examples.

Numero Uno

CREATE OR ALTER FUNCTION dbo.YearDiff(@d DATETIME)
RETURNS INT  
WITH SCHEMABINDING,   
     RETURNS NULL ON NULL INPUT  
AS
BEGIN
DECLARE @YearDiff INT;

SET @YearDiff = DATEDIFF(HOUR, @d, GETDATE())

RETURN @YearDiff  
END;  
GO

This function can’t be inlined. It uses the GETDATE function directly in a calculation.

I’m not bothered by that! After all, it’s documented.

In writing.

Numero Dos

CREATE OR ALTER FUNCTION dbo.i_YearDiff(@d DATETIME)
RETURNS INT  
WITH SCHEMABINDING,   
     RETURNS NULL ON NULL INPUT  
AS
BEGIN
DECLARE @YearDiff INT;
DECLARE @i DATETIME = GETDATE()

SET @YearDiff = DATEDIFF(HOUR, @d, @i)

RETURN @YearDiff  
END;  
GO

I was thinking that maybe if we just calculated the date once in a variable and then use that, we’d be able to inline the function.

But no.

No we can’t.

Numero Tres

CREATE OR ALTER FUNCTION dbo.NothingToSeeHere(@d DATETIME)
RETURNS INT  
WITH SCHEMABINDING,   
     RETURNS NULL ON NULL INPUT  
AS
BEGIN
DECLARE @YearDiff INT;
DECLARE @i DATETIME = GETDATE()

SET @YearDiff = 1;

RETURN @YearDiff  
END;  
GO

What if we don’t even touch GETDATE? Hm?

No.

Still no.

Kinda Weird, Right?


If you’re using SQL Server 2019 and want to find functions that can’t be inlined, start here:

SELECT OBJECT_NAME(m.object_id) AS object_name,
       m.is_inlineable
FROM sys.sql_modules AS m
    JOIN sys.objects AS o
        ON o.object_id = m.object_id
WHERE o.type = 'FN'
      AND m.is_inlineable = 0;

None of these functions can be inlined:

SQL Server Management Studio Query Results
Bummer.

Unfortunately, the only real solution here is to rewrite the function entirely as an inline table valued function.

CREATE OR ALTER FUNCTION dbo.InlineYearDiff(@d DATETIME)
RETURNS TABLE  
WITH SCHEMABINDING  
AS
RETURN
    SELECT DATEDIFF(HOUR, @d, GETDATE()) AS TimeDiff
GO

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



2 thoughts on “SQL Server 2019: What Kind Of Scalar Functions Can’t Be Inlined?

  1. I think this comes down to how scalar functions are RBAR. A scalar function that just returns GETDATE() will provide different values (depending on time elapsed). On my tally table of 1M rows, I got ~275 distinct values. With a TVF version, I get a single value.

    Of course, people probably want the TVF functionality anyway.

Comments are closed.