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 supportGREATEST/LEAST
- Similar to MAX/MIN, but operates across columns instead of across rowsGENERATE_SERIES
- Produces a set-based sequence of numeric valuesJSON functions
- Including functions to test whether a string is valid JSON, construct a JSON object and construct a JSON arrayDATE_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.