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. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
