I upgraded our primary OLTP box to SQL Server 2022 about eight months ago, mostly for the Query Store plan-forcing improvements. The T-SQL additions were supposed to be a side dish. They turned out to be the reason half my team is finally happy with their code reviews. Below are the five 2022-era functions I actually reach for every week, what they replaced in our codebase, and the results you can expect when you run them. None of this is theoretical — every example here is a sanitized version of something I ripped out of a production query in the last quarter.
1. GENERATE_SERIES(): killing the tally table
We had a user-defined tally table (dbo.Numbers, one million rows) that every reporting proc joined against to fill date gaps. It cost us roughly 1.4 GB of buffer pool on a server where buffer pool was already tight. GENERATE_SERIES lets me drop it.
SELECT value FROM GENERATE_SERIES(1, 5);
The results show a table with rows from 1 to 5:
value ----- 1 2 3 4 5
The real win is the date-spine pattern we use in every finance report. Imagine a sales table where some days have zero orders — we still need those days on the chart. Before 2022 I'd do a recursive CTE with a MAXRECURSION hint, or join dbo.Numbers. Now I use this code, joining with the result from GENERATE_SERIES():
DECLARE @start date = '2025-01-01', @end date = '2025-01-07';
SELECT DATEADD(DAY, gs.value, @start) AS CalendarDate,
ISNULL(s.OrderCount, 0) AS OrderCount
FROM GENERATE_SERIES(0, DATEDIFF(DAY, @start, @end)) gs
LEFT JOIN (
SELECT CAST(OrderDate AS date) AS d, COUNT(*) AS OrderCount
FROM Sales.Orders
WHERE OrderDate >= @start AND OrderDate < DATEADD(DAY, 1, @end)
GROUP BY CAST(OrderDate AS date)
) s ON s.d = DATEADD(DAY, gs.value, @start)
ORDER BY CalendarDate;The results on our demo data:
CalendarDate OrderCount ----------- ---------- 2025-01-01 142 2025-01-02 0 2025-01-03 198 2025-01-04 0 2025-01-05 0 2025-01-06 221 2025-01-07 187
A quick STATISTICS IO comparison on the full version of this query (a year of data, ~3.2M orders): the tally-table version did 18,114 logical reads against dbo.Numbers; the GENERATE_SERIES version did zero. Execution time dropped from 412 ms to 227 ms on my laptop. Not life-changing, but multiply it by the hundred reports that use this pattern and it adds up. One gotcha: GENERATE_SERIES returns bigint. If you're plugging the values into an int column downstream, cast them, or you'll get an implicit conversion warning in the plan.
2. GREATEST() and LEAST(): the end of three-branch CASE
We have a shipping table with three candidate dates per order: EstimatedShipDate, RequestedDate, and ApprovedDate. "When is the order actually committed to ship?" is the latest of the three. Our old code had two WHEN and one ELSE clauses in the CASE:
-- old way, and we had a bug in this for two years
SELECT OrderId,
CASE WHEN EstimatedShipDate >= RequestedDate
AND EstimatedShipDate >= ApprovedDate THEN EstimatedShipDate
WHEN RequestedDate >= ApprovedDate THEN RequestedDate
ELSE ApprovedDate
END AS CommitDate
FROM Orders;The bug: when any column was NULL the CASE silently returned the wrong branch. Nobody noticed until a customer complained about a three-month-old order showing "ship immediately." The 2022 version:
SELECT OrderId,
GREATEST(EstimatedShipDate, RequestedDate, ApprovedDate) AS CommitDate,
LEAST (EstimatedShipDate, RequestedDate, ApprovedDate) AS EarliestPromise
FROM Orders;Result:
OrderId CommitDate EarliestPromise ------- ---------- --------------- 1001 2025-03-14 2025-03-10 1002 2025-03-18 2025-03-12 1003 NULL NULL 1004 2025-03-22 2025-03-20
1004 2025-03-22 2025-03-20
Notice row 1003. GREATEST/LEAST return NULL if any argument is NULL, which is actually the ANSI behavior I want on a data-quality-sensitive column — it surfaces the bad row instead of hiding it inside a CASE branch. If you genuinely want to ignore NULLs, wrap each argument in ISNULL with a sentinel ('1900-01-01' for "smallest", '9999-12-31' for "largest"). Don't use COALESCE across the whole expression; you'll get the wrong answer. Where we use these now, outside date columns: finding the highest of Bid1/Bid2/Bid3 in an auction audit query, and picking the worst latency across three monitoring probes.
3. DATE_BUCKET(): time-series grouping without the DATEDIFF gymnastics
We ingest about 8 million IoT sensor readings a day into a staging table. The dashboard wants 15-minute averages. The old truncate-with-arithmetic pattern worked but nobody on the team could read it:
-- old: rounds ReadingTimestamp down to the nearest 15 minutes
SELECT DATEADD(MINUTE,
(DATEDIFF(MINUTE, 0, ReadingTimestamp) / 15) * 15,
0) AS BucketStart,
AVG(Temperature) AS AvgTemp
FROM SensorReadings
GROUP BY DATEADD(MINUTE, (DATEDIFF(MINUTE,0,ReadingTimestamp)/15)*15, 0);The 2022 version uses DATE_BUCKET() with buckets of 15 minutes:
SELECT DATE_BUCKET(MINUTE, 15, ReadingTimestamp) AS BucketStart,
AVG(Temperature) AS AvgTemp,
COUNT(*) AS Readings
FROM SensorReadings
WHERE ReadingTimestamp >= '2026-04-22'
AND ReadingTimestamp < '2026-04-23'
GROUP BY DATE_BUCKET(MINUTE, 15, ReadingTimestamp)
ORDER BY BucketStart;Result (first four rows):
BucketStart AvgTemp Readings --------------------- ------- -------- 2026-04-22 00:00:00 21.43 892 2026-04-22 00:15:00 21.39 901 2026-04-22 00:30:00 21.41 887 2026-04-22 00:45:00 21.48 895
The fourth parameter — origin — is where this gets powerful. Our operations team defines "day" as 06:00 to 06:00 (shift handover), not midnight. With the old pattern that took a subquery. With DATE_BUCKET it's an argument, passing in the 4th parameter as 6:00am:
SELECT DATE_BUCKET(HOUR, 24, EventTime, '2026-01-01T06:00:00') AS ShiftStart,
COUNT(*) AS Events
FROM Events
GROUP BY DATE_BUCKET(HOUR, 24, EventTime, '2026-01-01T06:00:00');Plan-shape note: DATE_BUCKET is sargable against a computed-column index on the same expression, but it is not sargable against a raw datetime column. If you want the seek, you still need the computed column + index. I lost an afternoon to that before realizing it.
4. WINDOW clause: the refactor I've been waiting ten years for
Our HR reporting proc had this pattern repeated four times in a single SELECT:
OVER (PARTITION BY DepartmentId ORDER BY Salary DESC)
Someone changed the partition to include LocationId for a one-off report, missed one of the four spots, and the numbers were wrong for a week. The WINDOW clause fixes this by letting you name the spec once:
SELECT DepartmentId,
EmployeeName,
Salary,
ROW_NUMBER() OVER w AS RowNum,
RANK() OVER w AS SalaryRank,
SUM(Salary) OVER w AS RunningTotal,
LAG(Salary) OVER w AS PreviousSalary
FROM Employees
WINDOW w AS (PARTITION BY DepartmentId ORDER BY Salary DESC);We get the same results, but the code is cleaner.
DepartmentId EmployeeName Salary RowNum SalaryRank RunningTotal PreviousSalary ------------ ------------ ------- ------ ---------- ------------ -------------- 10 Alvarez 142000 1 1 142000 NULL 10 Chen 128000 2 2 270000 142000 10 Okafor 119500 3 3 389500 128000 20 Bianchi 155000 1 1 155000 NULL 20 Duarte 131000 2 2 286000 155000 You can even layer windows:
You can even layer windows:
SELECT DepartmentId, EmployeeName, Salary,
SUM(Salary) OVER winRows AS RunningTotal,
AVG(Salary) OVER winRange AS DeptAvg
FROM Employees
WINDOW winBase AS (PARTITION BY DepartmentId ORDER BY Salary DESC),
winRows AS (winBase ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
winRange AS (winBase RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);When we refactored that HR proc the diff was +3/-19 lines, no logic change, and a junior dev found a second bug that had been hiding in one of the duplicated OVER clauses. Heads up: SSMS 18 and older IntelliSense doesn't know about the WINDOW keyword and will underline it angrily. The query runs fine; update to SSMS 20 and the squiggles go away.
5. IS [NOT] DISTINCT FROM: the NULL-safe join I gave up on
ETL code is full of this anti-pattern because ANSI NULL semantics make a = b return UNKNOWN when either side is NULL:
-- old way, and we had three variants of it across the warehouse
ON a.LastName = b.LastName
AND (a.MiddleName = b.MiddleName
OR (a.MiddleName IS NULL AND b.MiddleName IS NULL))Multiply that by every nullable column in a staging-to-target compare and your ON clause becomes unreadable. The 2022 way uses IS NOT DISTINCT FROM:
SELECT a.CustomerId AS StageId, b.CustomerId AS TargetId FROM Stage.Customers a JOIN dim.Customers b ON a.LastName = b.LastName AND a.MiddleName IS NOT DISTINCT FROM b.MiddleName AND a.Suffix IS NOT DISTINCT FROM b.Suffix;
Result on a small sample:
StageId TargetId ------- -------- 501 1201 -- both MiddleName='Lee' 502 1202 -- both MiddleName=NULL, matched correctly 503 1203 -- both Suffix=NULL 504 NULL -- stage has Suffix='Jr', target has NULL, correctly not matched
Row 502 is the one that matters. Under the old = comparison that row would not have matched, and your SCD Type-1 merge would have inserted a duplicate dim row every nightly run. We had exactly that bug in our customer dimension for about 14 months; the fix was a one-line change after upgrading. Performance note: IS NOT DISTINCT FROM is currently not sargable on nullable indexed columns — the optimizer won't use an index seek across it. For large warehouse merges I still pre-filter on the NOT NULL columns first and let IS NOT DISTINCT FROM handle only the nullable ones. Microsoft has a connect item open to improve this; I'll believe it when I see it in a CU.
Should you bother?
If you're on 2019 or earlier, none of this is a reason on its own to upgrade — but combined with Query Store on secondaries, parameter-sensitive plan optimization, and the IntelliSense fixes, it starts to add up. If you're already on 2022 or Azure SQL DB and you're still writing recursive CTEs to generate numbers or three-branch CASE expressions to pick a max, you're leaving readability and, in a few spots, real performance on the table. Pick the one that maps to code you already hate. Mine was DATE_BUCKET; yours is probably IS NOT DISTINCT FROM. Swap one proc over, run it in a dev environment, and compare the plan and the row count against the old version. That's the whole migration path.
