SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Question

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.
UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;
RANDWORD grabs a random word from a table containing approximately 100,000 English words.

BradC · Answer

I suspect this is because of a new SQL 2019 feature, scalar UDF inlining:

With this new feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. These expressions and subqueries are then optimized. As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.

This can be a performance benefit, but in your case it appears to be processing the function only once, then applying the same result to every row.
The bottom of the linked article describes several ways to disable scalar UDF inlining.
You can disable it database by database:
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or force this option query-by-query, using a hint:
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Or you can disable it by altering the UDF itself:
WITH INLINE = OFF

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

One Answer

Add your own answers!

Ask a Question