What SQL Server 2022 Added to T-SQL: IS DISTINCT FROM, GREATEST/LEAST, WINDOW, GENERATE_SERIES, DATE_BUCKET and JSON Functions

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database at compatibility levels 150, 160 and 170; PostgreSQL and MySQL comparisons were tried on PostgreSQL 18.6 and MySQL 8.4.

Guess First

Phone updates from the CRM (Customer Relationship Management) system have been loaded into the customer_updates table. We want to find which customers' phone numbers really changed. The setup and the current state:

DROP TABLE IF EXISTS dbo.customer_updates;
CREATE TABLE dbo.customer_updates (
    customer_id INT         NOT NULL PRIMARY KEY,
    phone       VARCHAR(20) NULL
);
INSERT INTO dbo.customer_updates (customer_id, phone) VALUES
    (1, '+44 7700 900123'),
    (2, NULL),
    (4, NULL),
    (5, '+90 555 012 91 77'),
    (6, '+49 151 2345678');
SELECT u.customer_id, c.phone AS current_phone, u.phone AS new_phone
FROM dbo.customer_updates AS u
JOIN dbo.customers AS c ON c.customer_id = u.customer_id
ORDER BY u.customer_id;

customer_id current_phone     new_phone
----------- ----------------- -----------------
          1 NULL              +44 7700 900123
          2 NULL              NULL
          4 +90 555 017 95 53 NULL
          5 +90 555 012 91 77 +90 555 012 91 77
          6 NULL              +49 151 2345678

Customers 1 and 6 had no phone and now have one. Customer 4's phone was removed. For customer 2 both sides are NULL, for customer 5 both are the same. So there are three changes. How many does this query count?

SELECT COUNT(*) AS changed
FROM dbo.customer_updates AS u
JOIN dbo.customers AS c ON c.customer_id = u.customer_id
WHERE u.phone <> c.phone;

changed
-------
      0

Zero. In all three changes one side is NULL, and by the three-valued logic from post 06 of this series NULL <> 'value' returns UNKNOWN; WHERE does not let it through. In sync jobs, audit tables and "find the changed rows" queries this bug lives unnoticed for years. SQL Server 2022 brought the standard solution: IS DISTINCT FROM. This post covers the language features SQL Server 2022 added to T-SQL (Transact-SQL), with before/after code and which ones require a compatibility level.

IS [NOT] DISTINCT FROM

Before SQL Server 2022 the correct query was written like this:

SELECT u.customer_id
FROM dbo.customer_updates AS u
JOIN dbo.customers AS c ON c.customer_id = u.customer_id
WHERE u.phone <> c.phone
   OR (u.phone IS NULL AND c.phone IS NOT NULL)
   OR (u.phone IS NOT NULL AND c.phone IS NULL)
ORDER BY u.customer_id;

customer_id
-----------
          1
          4
          6

Two extra conditions for every nullable column. In a sync query comparing five columns that means fifteen lines, and forgetting one is very easy. With 2022:

SELECT u.customer_id
FROM dbo.customer_updates AS u
JOIN dbo.customers AS c ON c.customer_id = u.customer_id
WHERE u.phone IS DISTINCT FROM c.phone
ORDER BY u.customer_id;

customer_id
-----------
          1
          4
          6

IS DISTINCT FROM treats two NULLs as equal and NULL versus a value as different, and its result is always TRUE or FALSE, never UNKNOWN. IS NOT DISTINCT FROM is NULL-safe equality; it is useful when joining two nullable columns. The predicate entered the standard with SQL:1999; PostgreSQL has supported it for a long time (in my test 1 IS DISTINCT FROM NULL returned true). MySQL 8.4 does not have it; instead it has its own NULL-safe equality operator <=>, and NOT (a <=> b) does the same job.

GREATEST and LEAST

A customer's "last activity" date comes from three sources: the registration date, the last order and the last review. Before 2022, taking the largest of several columns meant either nested CASE expressions or this trick:

WITH activity AS (
    SELECT c.customer_id, c.created_at,
           (SELECT MAX(o.ordered_at)  FROM dbo.orders  AS o WHERE o.customer_id = c.customer_id) AS last_order,
           (SELECT MAX(r.reviewed_at) FROM dbo.reviews AS r WHERE r.customer_id = c.customer_id) AS last_review
    FROM dbo.customers AS c
    WHERE c.customer_id IN (3, 7, 38)
)
SELECT customer_id,
       (SELECT MAX(v) FROM (VALUES (created_at), (last_order), (last_review)) AS x(v)) AS last_activity
FROM activity
ORDER BY customer_id;

customer_id last_activity
----------- -------------------
          3 2026-06-20 16:58:05
          7 2026-02-19 15:36:21
         38 2026-06-29 07:58:00

Turning columns into rows with VALUES and taking MAX works, but it hides the intent. With 2022:

WITH activity AS (
    SELECT c.customer_id, c.created_at,
           (SELECT MAX(o.ordered_at)  FROM dbo.orders  AS o WHERE o.customer_id = c.customer_id) AS last_order,
           (SELECT MAX(r.reviewed_at) FROM dbo.reviews AS r WHERE r.customer_id = c.customer_id) AS last_review
    FROM dbo.customers AS c
    WHERE c.customer_id IN (3, 7, 38)
)
SELECT customer_id, created_at, last_order, last_review,
       GREATEST(created_at, last_order, last_review) AS last_activity
FROM activity
ORDER BY customer_id;

customer_id created_at          last_order          last_review         last_activity
----------- ------------------- ------------------- ------------------- -------------------
          3 2024-06-23 13:50:00 2026-06-20 16:58:05 2026-05-05 10:33:45 2026-06-20 16:58:05
          7 2024-07-22 10:34:00 2026-02-19 15:36:21 2026-02-02 06:42:52 2026-02-19 15:36:21
         38 2026-06-29 07:58:00 NULL                NULL                2026-06-29 07:58:00

Customer 38 has no orders and no reviews; GREATEST ignored the NULLs and returned the registration date. In SQL Server GREATEST and LEAST skip NULL and return NULL only when every argument is NULL. PostgreSQL behaves the same (GREATEST(1, NULL, 3) returned 3). In MySQL, however, a single NULL argument makes the result NULL (the same expression returned NULL). Both functions entered the standard with SQL:2023 (T054), but the NULL behaviour varies by dialect; if you write portable code, guard with COALESCE.

The most common use of LEAST is capping a value:

SELECT book_id, title, list_price,
       LEAST(list_price, 500.00) AS capped_price
FROM dbo.books
WHERE book_id IN (26, 57, 65);

book_id title                           list_price capped_price
------- ------------------------------- ---------- ------------
     26 Dune                                390.50       390.50
     57 The Pragmatic Programmer           1150.00       500.00
     65 The Art of Computer Programming    1450.00       500.00

GENERATE_SERIES

In post 12 of this series we produced a date series with a recursive CTE (Common Table Expression):

WITH n AS (
    SELECT 0 AS i
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 6
)
SELECT DATEADD(day, i, CAST('2025-12-20' AS date)) AS d FROM n;

d
----------
2025-12-20
2025-12-21
2025-12-22
2025-12-23
2025-12-24
2025-12-25
2025-12-26

With 2022 the same job is one line:

SELECT DATEADD(day, value, CAST('2025-12-20' AS date)) AS d
FROM GENERATE_SERIES(0, 6);

d
----------
2025-12-20
2025-12-21
2025-12-22
2025-12-23
2025-12-24
2025-12-25
2025-12-26

GENERATE_SERIES(start, stop [, step]) is a table-valued function; it returns a single column named value. The step can be negative:

SELECT value FROM GENERATE_SERIES(100, 0, -25);

value
-----
  100
   75
   50
   25
    0

The recursive CTE's 100-iteration limit does not apply here, and the optimizer produces the series as a single operator. It is the natural tool for generating calendars, time slots or test data; in the SQL Server Derinlemesine (SQL Server in Depth) series we will use it to grow Bookstore to millions of rows. GENERATE_SERIES is not in the standard; its name and idea come from PostgreSQL. In SQL Server it requires compatibility level 160 (we try that below).

DATETRUNC and DATE_BUCKET

Three generations of ways to truncate a date to the start of the month:

SELECT DATEADD(month, DATEDIFF(month, 0, ordered_at), 0) AS month_start_old,
       DATEFROMPARTS(YEAR(ordered_at), MONTH(ordered_at), 1) AS month_start_parts,
       DATETRUNC(month, ordered_at) AS month_start_new
FROM dbo.orders
WHERE order_id = 250;

month_start_old         month_start_parts month_start_new
----------------------- ----------------- -------------------
2025-12-01 00:00:00.000 2025-12-01        2025-12-01 00:00:00

The DATEADD(month, DATEDIFF(month, 0, x), 0) trick is everywhere in old code bases and its result is always datetime (note the millisecond precision .000). DATEFROMPARTS returns date. 2022's DATETRUNC keeps the input type; here datetime2(0) went in and datetime2(0) came out. It truncates from year down to microsecond (year, quarter, month, week, day, hour, minute, second...).

DATE_BUCKET goes a step further: it splits time into fixed-width buckets. Orders in December 2025 in weekly buckets:

SELECT DATE_BUCKET(week, 1, CAST(ordered_at AS date)) AS week_start,
       COUNT(*) AS orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-01' AND ordered_at < '2026-01-01'
GROUP BY DATE_BUCKET(week, 1, CAST(ordered_at AS date))
ORDER BY week_start;

week_start orders
---------- ------
2025-12-01     12
2025-12-08      8
2025-12-15      7
2025-12-22     11
2025-12-29      4

The default starting point (origin) is 1900-01-01, which was a Monday; that is why weeks start on Monday. You can change the origin with the fourth argument; to start the week on Sunday, give a Sunday date:

SELECT DATE_BUCKET(week, 1, CAST(ordered_at AS date), CAST('2025-11-30' AS date)) AS week_start,
       DATENAME(weekday, DATE_BUCKET(week, 1, CAST(ordered_at AS date), CAST('2025-11-30' AS date))) AS day_name,
       COUNT(*) AS orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-01' AND ordered_at < '2026-01-01'
GROUP BY DATE_BUCKET(week, 1, CAST(ordered_at AS date), CAST('2025-11-30' AS date))
ORDER BY week_start;

week_start day_name orders
---------- -------- ------
2025-11-30 Sunday       11
2025-12-07 Sunday        9
2025-12-14 Sunday        5
2025-12-21 Sunday       12
2025-12-28 Sunday        5

DATE_BUCKET(week, 2, ...) produces two-week buckets, DATE_BUCKET(minute, 15, ...) 15-minute buckets; in time-series reports it saves you from DATEPART arithmetic. DATETRUNC and DATE_BUCKET are not standard; their PostgreSQL counterparts are date_trunc and date_bin.

TRIM with Characters and Ordered STRING_SPLIT

Before 2022, LTRIM and RTRIM removed only spaces. Now you can pass the characters to remove:

SELECT LTRIM('000123', '0')              AS ltrim_zeros,
       RTRIM('Dune...', '.')             AS rtrim_dots,
       TRIM('-' FROM '--978-1-23--')     AS trim_dashes,
       TRIM(LEADING '+' FROM '+90 555')  AS trim_leading;

ltrim_zeros rtrim_dots trim_dashes trim_leading
----------- ---------- ----------- ------------
        123 Dune       978-1-23    90 555

The TRIM(LEADING ... FROM ...) form is the standard's syntax; the LTRIM(x, chars) form is the same idea as SQL:2023's multi-character trim functions (T056). Careful: the LEADING/TRAILING/BOTH syntax requires compatibility level 160, the two-argument LTRIM/RTRIM does not.

STRING_SPLIT has existed since 2016 but did not guarantee the order of the parts; it was unreliable when you needed "the first tag" or "the third part". Since 2022, passing 1 as the third argument also returns an ordinal column:

SELECT value, ordinal
FROM STRING_SPLIT('dune,arrakis,spice,fremen', ',', 1)
ORDER BY ordinal;

value   ordinal
------- -------
dune          1
arrakis       2
spice         3
fremen        4

JSON_OBJECT, JSON_ARRAY and JSON_PATH_EXISTS

Since SQL Server 2016 you could produce JSON (JavaScript Object Notation) with FOR JSON, but that turns the query's whole result into JSON. 2022's constructors let you build JSON inside a single value:

SELECT JSON_OBJECT('id': b.book_id,
                   'title': b.title,
                   'price': b.list_price,
                   'authors': JSON_ARRAY(a1.last_name, a2.last_name),
                   'subtitle': b.subtitle NULL ON NULL) AS book_json
FROM dbo.books AS b
JOIN dbo.book_authors AS ba1 ON ba1.book_id = b.book_id AND ba1.author_order = 1
JOIN dbo.authors AS a1       ON a1.author_id = ba1.author_id
JOIN dbo.book_authors AS ba2 ON ba2.book_id = b.book_id AND ba2.author_order = 2
JOIN dbo.authors AS a2       ON a2.author_id = ba2.author_id
WHERE b.book_id = 42;

book_json
----------------------------------------------------------------------------------------------
{"id":42,"title":"Good Omens","price":340.90,"authors":["Pratchett","Gaiman"],"subtitle":null}

JSON_ARRAY put the two authors into an array, and NULL ON NULL wrote the NULL subtitle as null. That is also the default; ABSENT ON NULL leaves the key out entirely:

SELECT JSON_OBJECT('title': title, 'subtitle': subtitle ABSENT ON NULL) AS absent_on_null,
       JSON_OBJECT('title': title, 'subtitle': subtitle)                AS default_behaviour
FROM dbo.books
WHERE book_id = 26;

absent_on_null   default_behaviour
---------------- --------------------------------
{"title":"Dune"} {"title":"Dune","subtitle":null}

In a payload going to an API (Application Programming Interface) this difference matters: the client may behave differently for "field missing" and "field null". JSON_PATH_EXISTS tells you whether a path exists, and ISJSON gained a type check:

SELECT JSON_PATH_EXISTS('{"title":"Dune","price":390.5}', '$.price')  AS has_price,
       JSON_PATH_EXISTS('{"title":"Dune","price":390.5}', '$.isbn')   AS has_isbn,
       ISJSON('42')          AS any_type_default,
       ISJSON('42', SCALAR)  AS is_scalar,
       ISJSON('42', OBJECT)  AS is_object;

has_price has_isbn any_type_default is_scalar is_object
--------- -------- ---------------- --------- ---------
        1        0                0         1         0

ISJSON('42') returned 0 by default, because to stay compatible with the old behaviour it only considers objects and arrays valid; passing SCALAR makes it accept single values as valid JSON too. JSON_OBJECT and JSON_ARRAY come from the SQL/JSON part of the standard in SQL:2016. SQL Server 2025's native json type and the JSON_OBJECTAGG/JSON_ARRAYAGG aggregates are the subject of the next post.

APPROX_PERCENTILE_CONT and the Rest

In post 14 of this series we computed the median with PERCENTILE_CONT; that function sorts all values and works only as a window function. 2022's APPROX_PERCENTILE_CONT, on the other hand, is a normal aggregate and works with a sketch called KLL (the quantile sketch algorithm published by Karnin, Lang and Liberty in 2016):

WITH order_totals AS (
    SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    GROUP BY o.order_id
)
SELECT APPROX_PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS approx_median,
       (SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) OVER () FROM order_totals) AS exact_median
FROM order_totals;

approx_median exact_median
------------- ------------
       802.62       802.62

On 380 orders the result is exactly the same. According to Microsoft Learn this function gives a rank-based error guarantee: at most 1.33% error with 99% confidence. Because the algorithm uses random sampling, on large data the result can vary slightly from run to run. It is good for showing the p95 (95th percentile) duration of millions of rows on a dashboard; not for invoicing.

2022's language additions do not end there. The WINDOW clause we used in post 14, IGNORE NULLS in FIRST_VALUE/LAST_VALUE, and bit manipulation functions such as LEFT_SHIFT, RIGHT_SHIFT, BIT_COUNT, GET_BIT and SET_BIT arrived with the same release.

Production Notes: Compatibility Level

Every database has a compatibility level, and some new syntax only switches on above a certain level. On SQL Server 2022 new databases are created at 160, on 2025 at 170; but a database restored or upgraded from an older server keeps its old level. I lowered my own database to 150 (the SQL Server 2019 level) and tried:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

SELECT value FROM GENERATE_SERIES(1, 3);

Msg 208, Level 16, State 1, Server b8be943222fa, Line 1
Invalid object name 'GENERATE_SERIES'.

SELECT title, MAX(list_price) OVER w AS max_price
FROM dbo.books
WINDOW w AS (PARTITION BY category_id);

Msg 102, Level 15, State 1, Server b8be943222fa, Line 3
Incorrect syntax near 'w'.

SELECT TRIM(LEADING '0' FROM '007');

Msg 102, Level 15, State 1, Server b8be943222fa, Line 1
Incorrect syntax near '0'.

The error messages are misleading: "Invalid object name 'GENERATE_SERIES'" or "Incorrect syntax near 'w'" say nothing about the compatibility level. At the same level these three ran without a problem:

SELECT GREATEST(1, 5, 3) AS greatest_ok,
       DATETRUNC(year, CAST('2025-12-22' AS date)) AS datetrunc_ok,
       CASE WHEN 1 IS DISTINCT FROM NULL THEN 'yes' END AS distinct_ok;

greatest_ok datetrunc_ok distinct_ok
----------- ------------ -----------
          5 2025-01-01   yes

In my tests, on a level-150 database on SQL Server 2025, only GENERATE_SERIES, the WINDOW clause and the TRIM(LEADING/TRAILING/BOTH ...) syntax from this post failed; IS DISTINCT FROM, GREATEST/LEAST, DATETRUNC, DATE_BUCKET, two-argument LTRIM/RTRIM, STRING_SPLIT's ordinal, the JSON functions and APPROX_PERCENTILE_CONT worked. Raising the level to 160:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;

SELECT value FROM GENERATE_SERIES(1, 3);

value
-----
    1
    2
    3

One more warning: the compatibility level changes not only syntax but also optimizer behaviour (the cardinality estimator, intelligent query processing features). Raising the level "to be able to use a new function" is a decision that can affect the plans of all queries; do not change it in production without a before/after comparison in Query Store. Details are in the SQL Server Derinlemesine series. And the most basic trap: none of these functions exists on SQL Server 2019, whatever the compatibility level. If your code must run on both 2019 and 2022 servers, write for the lowest version.

Common Pitfalls

Detecting changes by comparing nullable columns with <>. Every change involving NULL is lost; use IS DISTINCT FROM.

Assuming GREATEST's NULL behaviour is the same in every dialect. SQL Server and PostgreSQL skip NULL, MySQL returns NULL.

Blaming GENERATE_SERIES's "Invalid object name" error on a missing table. Check the compatibility level in sys.databases first.

Raising the compatibility level just for a new function, without measuring plan regressions.

Forgetting that DATE_BUCKET starts weeks on Monday by default; if the report must start on Sunday, give an origin.

Not knowing that JSON_OBJECT writes NULL values as null by default; if your API contract expects the key to be absent, write ABSENT ON NULL.

A Small Experiment

★☆☆ Add a city column to the customer_updates table and find customers whose phone or city changed using IS DISTINCT FROM. Write the same query with pre-2022 syntax and compare the line counts.

★★☆ Use GENERATE_SERIES and DATE_BUCKET to produce every week of 2025 (weeks without orders should show 0) and return the order count for each week.

★★★ Produce JSON of the form {"id": ..., "title": ..., "authors": [...]} for every book with JSON_OBJECT; since the number of authors varies from book to book, JSON_ARRAY will not be enough. Which method can solve this on 2022? Keep your solution to compare with JSON_ARRAYAGG in the next post.

SQL Server 2022 was a release that moved the language closer to the standard. 2025 went further down that road: regular expression functions, the native json type, the || operator and new aggregates like PRODUCT. In the next post we try them, again together with their compatibility level requirements.