Hi Steve,

I just reported this as bug on feedback.azure.com

-> https://feedback.azure.com/d365community/idea/ba4b34ea-1d87-ef11-9442-6045bd8115dc

Meanwhile we found a few other workarounds to make it work again.

* change the auto-cleanup from true => false => true

*...

Thanks for your input @steve-2, I checked the release notes of all the CU's before posting this message.

We narrowed down what could cause this behavior, and fix 2962248 (documented in https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate27#2962248)...

OK, got an update from Microsoft after raising a support ticket.

This is what they say :

I have a response from my colleague and this seems to be a defect.

We have...

Grant,

I don't see why the statement is incomplete ?

BOL indicates that ORDER BY <column> without a ASC or DESC is ASC by default.

And even when I add "order by column1...

Jeffrey,

Yes, using a TOP 1 fixes the problem. And that's the way i usually do it, but when you inherit a program with 7000 SP's and 1500 Scalar UDF, you...

Hi Grant,

That's the point, the statement does include an ORDER BY.

See my first post.

CREATE FUNCTION [dbo].[fnTest]()
RETURNS int
AS
BEGIN
       declare @myNumber INT;
 ...

Thx Mike,

You're right, when I execute below on SQL 2016 & SQL 2019, both return the same value.

So it's something with the function, and not the SQL body.

3 DataBase Admins walked into a NoSQL bar...

A little while later, they walked out because they couldn't find a table.