I’m currently reading a book about SQL Server 2014. It claims that User-Defined Scalar Valued Functions prevent parallelism for the entire plan that they appear in. Is this still true in later versions?
>Solution :
If the function is not inlined it still prevents parallelism.
TSQLUserDefinedFunctionsNotParallelizable still exists as a NonParallelPlanReason in the execution plan for 2022.
If the function is inlined the query can go parallel.
There is an example of that here
Here are some key observations from the above plan … SQL Server is
now using parallelism across all operators.
Scalar function inlining has been available since SQL Server 2019 but only some functions meet the criteria for this and there have been some issues with the implementation of the feature meaning that the criteria for what can be inlined has become more strict.
It is still the case that if a computed column in a table references a scalar UDF then this blocks paralellism – irrespective of whether or not the UDF can be inlined however.
DROP TABLE IF EXISTS dbo.T
GO
CREATE OR ALTER FUNCTION dbo.F1()
RETURNS INT
WITH INLINE = ON
AS
BEGIN
RETURN 1;
END;
GO
CREATE TABLE dbo.T
(
A INT
)
GO
SELECT SUM(dbo.F1())
from dbo.T t1 join dbo.T t2 ON t1.A = t2.A
option (use hint('enable_parallel_plan_preference'))
GO
ALTER TABLE dbo.T ADD B AS dbo.F1()
GO
SELECT SUM(dbo.F1())
from dbo.T t1 join dbo.T t2 ON t1.A = t2.A
option (use hint('enable_parallel_plan_preference'))
