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:

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.