SQL Server 2022 T-SQL Features Worth Using: GREATEST, DATE_BUCKET, IS DISTINCT FROM and More

SQL Server 2022 added a set of small T-SQL features that quietly remove years of workarounds. None of them are headline features, but you will use them every week once you know they exist. Most need database compatibility level 160 and are also available in Azure SQL Database.

SQL Server 2022 T-SQL Features Worth Using: GREATEST, DATE_BUCKET, IS DISTINCT FROM and More

GREATEST and LEAST

SELECT EmployeeID,
       GREATEST(LastLogin, LastPayslipView, LastProfileEdit) AS LastActivity,
       LEAST(BasicPay, PayCap) AS CappedPay
FROM dbo.EmployeeActivity;

No more nested CASE expressions or VALUES tricks to find the max across columns. NULLs are ignored unless all arguments are NULL.

IS [NOT] DISTINCT FROM

SELECT * FROM dbo.Employees e
JOIN staging.Employees s ON s.EmployeeID = e.EmployeeID
WHERE e.Email IS DISTINCT FROM s.Email;

A NULL-safe comparison: two NULLs are treated as equal, NULL vs a value as different. Perfect for change detection without ISNULL(col, '') hacks.

DATETRUNC

SELECT DATETRUNC(MONTH, OrderDate) AS MonthStart, SUM(Amount)
FROM dbo.Orders
GROUP BY DATETRUNC(MONTH, OrderDate);

Truncates a date to year, quarter, month, week, day, hour and so on, replacing the old DATEADD(MONTH, DATEDIFF(MONTH, 0, d), 0) idiom.

DATE_BUCKET

SELECT DATE_BUCKET(MINUTE, 15, EventTime) AS Slot, COUNT(*)
FROM dbo.ApiCalls
GROUP BY DATE_BUCKET(MINUTE, 15, EventTime);

Groups times into fixed-width buckets (every 15 minutes, every 2 weeks). Great for monitoring and time-series charts.

The WINDOW clause

SELECT EmployeeID, PayPeriod, GrossPay,
       SUM(GrossPay) OVER w AS RunningTotal,
       AVG(GrossPay) OVER w AS RunningAvg
FROM dbo.Payslips
WINDOW w AS (PARTITION BY EmployeeID ORDER BY PayPeriod
             ROWS UNBOUNDED PRECEDING);

Define a window once and reuse it, instead of repeating the same OVER clause.

STRING_SPLIT with ordinal, and TRIM with characters

SELECT value, ordinal FROM STRING_SPLIT('a,b,c', ',', 1);
SELECT TRIM('0' FROM '000123000');           -- '123'
SELECT LTRIM('xxABC', 'x');                  -- 'ABC'

The new enable_ordinal argument finally tells you the position of each item.

GENERATE_SERIES

SELECT value FROM GENERATE_SERIES(1, 12);

Produces a sequence of numbers without a tally table or recursive CTE.

JSON improvements

SELECT JSON_OBJECT('id': EmployeeID, 'name': Name) FROM dbo.Employees;
SELECT JSON_ARRAY(1, 2, 3);
SELECT * FROM dbo.Events WHERE ISJSON(Payload, OBJECT) = 1;

JSON_OBJECT and JSON_ARRAY build JSON directly, and ISJSON can check the type.

APPROX_PERCENTILE_CONT

Approximate median and percentiles on huge tables using very little memory, ideal for dashboards where exactness to the last row does not matter.

How to start using them

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;

Raise the level carefully (with Query Store watching for plan regressions), then start replacing old workarounds as you touch the code.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more