T-SQL enhancements in SQL Server 2022

Daily Knowledge Drop

There are a number of very useful T-SQL enhancements coming with SQL Server 2022 (currently in preview). Some of these include:

• STRING_SPLIT - splits a string into rows of substrings, based on a specified separator character, now with ordinal support
• GREATEST/LEAST - Similar to MAX/MIN, but operates across columns instead of across rows
• GENERATE_SERIES - Produces a set-based sequence of numeric values
• JSON functions - Including functions to test whether a string is valid JSON, construct a JSON object and construct a JSON array
• DATE_BUCKET - Returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined

Below we'll explore some of the enhancements I will find most useful (based on my previous experiences and requirements), while additional details on the other enhancements can be found in the references below.

STRING_SPLIT

The SPLIT_STRING function, first introduced with SQL Server 2016, allows for the splitting a string into rows of substrings - especially useful for developers making extensive use of stored procedures (for example, with SQL Server Reporting Services)

For example, executing the following SQL:

SELECT * from STRING_SPLIT('one,two,three,four,five', ',')

Results in the following output:

value
-----------------------
one
two
three
four
five

Coming in SQL Server 2022, is the ability to determine the nth item of the list.

For example, executing the following SQL:

SELECT * from STRING_SPLIT('one,two,three,four,five', ',' , 1) WHERE ordinal = 3

Results in the following output:

value                   ordinal
----------------------- --------------------
three                   3

An additional parameter, enable_ordinal is available which will return an ordinal column - which can then be used as part of a WHERE clause.

One limitation of the SPLIT_STRING remains, and that is that the delimiter can only be a single character, and not a string.

GREATEST/LEAST

The new GREATEST and LEAST functions, are similar to the existing MAX and MIN functions, but operates across columns instead of across rows.

Consider the following VisitorsPerMonth table which stores the number of unique visitors to a website, per month, per year.

SELECT * FROM VisitorsPerMonth

Year        Jan         Feb         March
----------- ----------- ----------- -----------
2020        435         643         763
2021        893         1121        1327
2022        1923        2107        2782

We can use Max to determine maximum number of visitors in any January (a max across rows):

SELECT MAX(Jan)
FROM VisitorsPerMonth

However, what if we wanted the max number of visitors in any particular month in a year - this requires an operation across columns, which is where the GREATEST function comes into play.

A number of columns are supplied to the function, and the greatest value will be returned:

SELECT Year, GREATEST(Jan, Feb, March) as HighestVisitors
FROM VisitorsPerMonth

This results in the following output:

Year        HighestVisitors
----------- ---------------
2020        763
2021        1327
2022        2782

We now know how many visitors the site had in its most popular month in each year.

GENERATE_SERIES

This new function produces a set-based sequence of numeric values based on specified START, STOP and STEP values.

The usage is fairly straightforward:

SELECT value FROM GENERATE_SERIES(START = 1, STOP = 3);
SELECT value FROM GENERATE_SERIES(START = 0, STOP = 25, STEP = 5);

Which results in the output:

value
-----------
1
2
3

value
-----------
0
5
10
15
20
25

If no STEP value is specified, a value of 1 is used.

Notes

As a C# developer, in recent years, my focus has been more on using Entity Framework Core and less on raw SQL usage - however, knowledge of these enhancement will come in useful when the need arises to do raw SQL (a custom query through EF, or Dapper, or a report stored procedure, for example).

References

My Favorite T-SQL Enhancements in SQL Server 2022
DATE_BUCKET (Transact-SQL)
JSON_OBJECT (Transact-SQL)

Daily Drop 111: 06-07-2022

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.