tsf_user inlineable

Related products: Software Factory

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 

not inlineable

 

Query with tsf_user not being executed parallel

When removing the use of variables in the function, the function is inlineable and the queries will run parallel when needed:

Inlineable = true
My heavy query with tsf_user goes parallel

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.


NewCompleted