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.
Perhaps I should add the link to the discussion about inlineability:
Hi Peter, depending on your platform version, the tsf_user function should be inlinable:
The use of variables has been removed in version 2021.3.
However, a platform upgrade by itself will not automatically replace the tsf_user() function on your own products.
The function should be re-applied during the first product upgrade when the tsf_user function is marked as changed compared to the previous version by the upgrade process.