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.
Best Answer
I suspect this is because of a new SQL 2019 feature, scalar UDF inlining:
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:
Or force this option query-by-query, using a hint:
Or you can disable it by altering the UDF itself: