What’s new in SQL Server 2022

[utterances-bot]

What’s new in SQL Server 2022 | Chad’s Blog

Taking a look at some of the new language enhancements coming in SQL Server 2022

https://chadbaldwin.net/2022/06/02/whats-new-in-sql-server-2022.html

[BartekR]

Great summary, thanks!

[samot1]

Regarding SUM() and AVG() in the GREATEST() / LEAST() section: for this stuff we don't really need special operations. It is not harder to write col_1 + col_2 + col_3 for sum and divide it by 3 for the AVG than to write SUM_/AVG_FUNCTION(col_1, col_2, col_3).

Only exception is handling of NULL values - if one of the columns is NULL and you don't use ISNULL every time, you get NULL as result (contrary e.g. to the CONCAT() function for string aggregation, where NULLs will be simply ignored.

[chadbaldwin]

@samot1 I never said it was hard, I just wanted to point out that SUM(), AVG() and COUNT() do not have row based counter parts like MIN() and MAX() do with LEAST() and GREATEST() respectivly.

And yeah, that NULL issue is why I usually just use what I referred to as the "old" method, because it handles that. I would rather type this:

SELECT ColA, ColB, ColC, y.[Avg]
FROM #event
    CROSS APPLY (
        SELECT [Avg] = AVG(x.val)
        FROM (VALUES (ColA), (ColB), (ColC)) x(val)
    ) y
WHERE y.[Avg] > 100
ORDER BY y.[Avg] DESC;

Vesus:

SELECT ColA, ColB, ColC, (COALESCE(ColA, 0) + COALESCE(ColB, 0) + COALESCE(ColC)) / 3 -- Also hardcoding the count?
FROM #event
WHERE (COALESCE(ColA, 0) + COALESCE(ColB, 0) + COALESCE(ColC)) / 3 > 100
ORDER BY (COALESCE(ColA, 0) + COALESCE(ColB, 0) + COALESCE(ColC)) / 3 DESC;

So while you don't need "special operations"...it still results in cleaner code and less duplicated/re-used code. I would also argue it's probably a little easier to deduce what is happening by seeing the use of AVG().

Sure, you could clean up the first method by writing it like this:

SELECT ColA, ColB, ColC, y.[Avg]
FROM #event
    CROSS APPLY (SELECT [Avg] = (COALESCE(ColA, 0) + COALESCE(ColB, 0) + COALESCE(ColC)) / 3)) y
WHERE y.[Avg] > 100
ORDER BY y.[Avg] DESC;

But I still prefer using the aggregate function, unless for some reason there is a performance issue with it.

[robinwilson16]

This is how I would normally generate a tally table/series prior to 2022 which I find easier than CTEs or XML:

SELECT 
	RN.RowNum
FROM (
	SELECT
		RowNum =
			ROW_NUMBER () OVER (
				ORDER BY
					OBJ.object_id
			)
	FROM SYS.all_objects OBJ
) RN
WHERE
	RN.RowNum <= 100

If thousands of items are needed then I join it back to itself

GENERATE_SERIES will be easier than them all though

[chadbaldwin]

@robinwilson16 that's a popular alternative, but I'm personally not a fan of it because the number of rows returned by all_objects is dependent upon permissions, and there isn't a reliable number of records returned by it. At least with the other methods, you know exactly how many rows will always be returned, and they are not impacted by permissions / permissions changes, especially from one environment to another.

[JeffModen]

"This GENERATE_SERIES() function is an absolute pig 🐷.:

Oh, lordy. I don't have the time to download the preview of 2022, etc, and am waiting for the RTM to hit the streets. As a result, I've not tested GENERATE_SERIES() and I've also not seen any other performance tests.

To be honest, I'm not shocked at what your tests show. I'm not ever disappointed. MS didn't even know what Erland Sommarskogs what talking about way back in the old 2008 (IIRC) connect days when he first suggested such a thing. Based on their track history with things like the FORMAT function and STRING_SPLIT() and PIVOT and rCTEs,, I kind of expected this type of really poor performance. I think a WHILE loop in a transaction might even beat it.

The only way that I can think of for why it's so slow is if they implemented it as a CLR function behind the scenes.

It look like Itzik's "GetNums" function and my fnTally function are still going to be useful for a while.

Thanks for the great article, Chad. I really appreciate it.