Does using a function calling GETDATE() in a view consistently give dramatically worse performance than using GETDATE() directly?

Question

I have a function that gets the Monday of the week @X weeks in the past/future. I bolted it together from other code on this website. I believe it goes like this:

CREATE FUNCTION [FOO]
    (@X INT)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(WEEK, @X, DATEADD(d, -((DATEPART(DW, GETDATE()))
END

I've recently found that if I call this function inside of a view, then I get dramatically worse performance than if I copy and pasted the above code instead. In other words, I find that directly using CAST(DATEADD(WEEK, [X VALUE HERE], DATEADD(d, -((DATEPART(DW, GETDATE())) AS DATE) to be far more performant than using FOO([X VALUE HERE]).

If the activity monitor is to be trusted, it's as if the value of GETDATE() is being forgotten when you use the user-defined function; I see my user-defined function being called a great many times when I try to query any views that use it.

Is there any known cause for this behavior? It's as if making functions with GETDATE() is always a bad idea. This question hints towards as much, but it's not a direct answer.

I've checked for any type mismatches of any sort. There is none. @@VERSION reports that I'm on a 2016 version.

Answer 1

@J.Mini, you said "as if making functions with GETDATE() is always a bad idea".

It is not about the GETDATE(). It is about any user-defined scalar function in SQL Server prior to 2019. Any user-defined scalar function in SQL Server prior to 2019 is a bad idea because of likely poor performance. When your code runs 10x or 100x slower your users will notice.

This makes the standard programming idiom "if you see yourself doing the same thing many times, then make it a function with a good name" to be a bad idea in T-SQL.

Other RDBMSs like Postgres and Oracle may behave differently and work perfectly fine performance-wise with user-defined functions.

It is just a "feature" (or, rather, a peculiarity) of SQL Server that you need to be aware of. Especially since you use this function in multiple places. All of these places (queries) are likely much slower than they could have been.

Here is a good article by Aaron Bertrand on this topic:

Encapsulating Common Code Into Scalar UDFs