As also discussed in the following link, since SQL Server 2019 inlineable scalar functions are an option. This can make sure a query can run parallel. The tsf_user function is not inlineable at this moment. When using this function in a heavy query, it will not be executed parallel as you can see in the images below.
Not inlineable
When removing the use of variables in the function, the function is inlineable and the queries will run parallel when needed:
The script I have used is:
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
CREATE OR ALTER FUNCTION tsf_user
()
RETURNS tsf_user
AS
BEGIN
--control_proc_id: sql_tsf_user
--template_id: sql_tsf_user
--prog_object_item_id: sql_tsf_user
--template_description: Returns context_info or system_user
RETURN CASE WHEN CONTEXT_INFO() IS NOT NULL THEN
CASE WHEN LEN(REPLACE(
CONVERT(
VARCHAR(128),
CONTEXT_INFO()
)COLLATE Latin1_General_BIN,
CHAR(0),
''
)
) <> 36 THEN
REPLACE(
CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN,
CHAR(0),
''
)
WHEN SERVERPROPERTY('EngineEdition') <> 5 THEN
REPLACE(
CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN,
CHAR(0),
''
)
WHEN REPLACE(
CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN,
CHAR(0),
''
)NOT LIKE '________-____-4___-____-____________' THEN
REPLACE(
CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN,
CHAR(0),
''
)
END
ELSE SYSTEM_USER
END;
END;
GO
Perhaps this is something you can use in a new version.
