At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022. Before the talk, I made a joke with him that none of these were new because it’s 2024. They’d been out for 2 years.
Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don’t have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What’s New in the Language.
I’m curious if any of you are using these new functions? There are a few time series ones, though I think GENERATE_SERIES is the one many of you might have used. Are any of you using DATE_BUCKET? That looks interesting, but I have to admit I haven’t played with it. STRING_SPLIT with the ordinal is my guess for the most popular function people use.
There are also some new JSON and bit manipulation functions, which might be of use in some situations. GREATEST/LEAST are there, but I’ll have to say these functions haven’t come to mind as solutions in any of my queries or answers I write for questions. I do use the trim functions, though still only with spaces. I guess that some of you might find ways to incorporate trimming with other characters and possibly change old code.
I do think that many of you can likely refactor code and make it cleaner with these functions, but you should test them extensively. As we’ve seen with some language changes, performance isn’t always better, and some changes (like FORMAT), can cause you resource issues. At the same time, if the code performs well, using cleaner code is a good way to perhaps update your codebase and gain some skill with new techniques.
One warning. While I like refactoring code, make sure you do some testing, preferably automated, to be sure you aren’t introducing bugs or missing edge cases that your old code covers well. A cleaner codebase is nice, but having working code is more important.
If you’re using any language features, leave a comment today.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
Note, podcasts are only available for a limited time online.
Voice of the DBA 
In my field, DateTrunc will be very handy. Even though there are ways to get the date portion only of a date/time value thanks to Itzah-Ben Gah, this is cleaner and assume it’s not drastically different performance wise I will be using this a lot once our accounting software vendor upgrades to supporting more recent version of SQL Server. I was surprised this was named DateTrunc as oppose to DateRound.
I am curious as to why one would use First_Value/Last_Value when the same or at least it appears the same, can be done using the classic Min/Max functions combined with OVER() which is also required for First_Value & Last_Value.
LikeLike
The first_value/last_value are useful when you want to get the value from an ordinal, where the ordering isn’t by the same value. For example, if I were to order by some numeric, but I wanted the first value for a string, I might get different values for min v first_Value. Same thign where I might order by date for sales. The first sales value (base value) might not be the min.
LikeLike
It’s not a new “function” but the IGNORE NULLS feature added to some functions has been very useful for some of my work, and eliminated the need for some subqueries.in places, which has been quite a nice little gain.
LikeLike
That looks interesting as an add in a few functions
LikeLike
I’ve found it very useful with LAG/LEAD when getting the last/next non-NULL value.
LikeLike
Pingback: New Syntax in SQL Server 2022 – Curated SQL