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;
GENERATE_SERIES:
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.
Microsoft SQL Docs – GENERATE_SERIES
DATETRUNC:
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.
Microsoft SQL Docs – DATETRUNC
DATE_BUCKET:
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;
Microsoft SQL Docs – DATE_BUCKET
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;
Microsoft SQL Docs – IS DISTINCT FROM
WINDOW:
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.
Other:
There are additional functions added for JSON and for bit manipulation. I plan to cover those in a later post.
Links:
SQL Rob 