There are several new or enhanced T-SQL commands added for the upcoming SQL Server 2022 release.
Microsoft – What’s New In SQL Server 2022
These code examples are running against version CTP 2.1, and using SSMS v19.0 Preview 2.
GREATEST and LEAST:
Return the greatest/least of values from 2 or more columns.
DROP TABLE IF EXISTS dbo.Temperature; CREATE TABLE dbo.Temperature( City varchar(20) NOT NULL, JuneTemperature tinyint NOT NULL, JulyTemperature tinyint NOT NULL, AugustTemperature tinyint NOT NULL ); INSERT INTO dbo.Temperature(City, JuneTemperature, JulyTemperature, AugustTemperature) VALUES ('Atlanta', 90, 95, 93), ('Phoenix', 110, 115, 113), ('Chicago', 85, 90, 92); SELECT City, JuneTemperature, JulyTemperature, AugustTemperature, LEAST(JuneTemperature, JulyTemperature, AugustTemperature) as LowTemp, GREATEST(JuneTemperature, JulyTemperature, AugustTemperature) as HighTemp FROM dbo.Temperature;
SELECT * FROM GENERATE_SERIES(1, 10);
In this example, GENERATE_SERIES will return 10 rows, with the numbers from 1 to 10 as values. I’ve used this a few times in Postgres, especially in generating test data.
SELECT * FROM GENERATE_SERIES(2, 10, 2);
You can also specify a third parameter, in this case to return all of the even numbers in the sequence 2 to 10.
DATETRUNC will truncate a date, using the specified date part.
SELECT DATETRUNC(MONTH, GETDATE());
In this case, the function will return a datetime of the first day of the current month.
DATE_BUCKET lets a user define time periods that can be used to group or aggregate data.
If we want to use one week as a bucket, this query will return a datetime with the start of the window for today’s date.
SELECT DATE_BUCKET(WEEK, 1, GETDATE());
We can also define the starting data to use for the buckets (The default is Jan 1, 1900).
SELECT DATE_BUCKET(WEEK, 1, GETDATE(), CAST('2022-01-01' as datetime));
This query will take dates and a count for that date, group them into date buckets of one week, and give the count sum for each window/bucket.
WITH cte_sales as ( SELECT * FROM (VALUES ('2022-10-01', 300), ('2022-09-30', 125), ('2022-10-10', 550), ('2022-10-20', 735), ('2022-10-05', 250), ('2022-10-07', 97), ('2022-10-11', 135), ('2022-10-21', 900) ) as t(date, count) ) SELECT DATE_BUCKET(WEEK, 1, CAST(s.date as date)) as bucket_start, SUM(s.count) as count_total FROM cte_sales as s GROUP BY DATE_BUCKET(WEEK, 1, CAST(s.date as date)) ORDER BY 1;
IS [NOT] DISTINCT FROM:
IS DISTINCT FROM gives a way to make a comparison that will takes NULLs into account.
This comparison won’t return a result, since we can’t compare a number to NULL:
SELECT 1 WHERE 1 <> NULL;
This query will return a result:
SELECT 1 WHERE 1 IS DISTINCT FROM NULL;
We can also use NOT in the comparison:
SELECT 1 WHERE 1 IS NOT DISTINCT FROM 1;
WINDOW lets us define a partition and order clause once, and refer to it with a Window function.
WITH cte_sales as ( SELECT * FROM (VALUES ('2022-09-30', 300), ('2022-09-30', 125), ('2022-10-01', 550), ('2022-10-01', 735), ('2022-10-01', 250), ('2022-10-02', 97), ('2022-10-02', 135), ('2022-10-02', 900) ) as t(date, count) ) SELECT s.date, ROW_NUMBER() OVER w as row_order, COUNT(s.count) OVER w as item_count, AVG(s.count) OVER w as item_avg FROM cte_sales as s WINDOW w as (PARTITION BY s.date ORDER BY s.count);
This allows us to define the PARTITION and ORDER BY values once, and use that clause for multiple window functions.
There are additional functions added for JSON and for bit manipulation. I plan to cover those in a later post.