T-SQL Features Added in Recent Versions

A familiar workaround can outlive the problem it solved. Recent T-SQL features give several everyday queries a clearer form when your target server supports them.

Check the Server Before Using New T-SQL Features

A feature list is useful only when it matches the SQL Server instances that run the code. Confirm engine version, edition where relevant, and database compatibility requirements. A script that works in one development database can fail in an older reporting environment.

I start with one repeated pattern that the team already uses. Replacing a long expression can improve readability, but a broad rewrite of working procedures creates unnecessary risk. Change a small surface, test output, then carry the pattern forward.

Ask which servers execute the same script library. A shared script should state its minimum supported version. A feature tour is not a license to surprise the oldest server in the estate.

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('Edition') AS Edition,
       compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

Use DATETRUNC for Period Boundaries

Older code commonly uses DATEADD with DATEDIFF to find a month or day start. DATETRUNC states the intent directly. It returns a truncated value based on the input type, so confirm how it joins to stored date keys. The main gain is that the reader can see the period boundary immediately.

I compare the old and new expressions at a month end and around a leap day. The expected output should match the report’s period rule. A clear function name does not settle whether a fiscal month follows calendar months.

Keep the old pattern in scripts that must run on older engines. Use the new one where the deployment baseline supports it. The query should declare its environment assumptions.

DECLARE @d datetime2(3) = '2025-03-18T14:22:00.000';
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0) AS OlderMonthStart,
       DATETRUNC(month, @d) AS NewerMonthStart;

Use DATE_BUCKET for Repeating Intervals

DATE_BUCKET groups times into intervals such as fifteen minutes or two weeks. Its origin sets the alignment. That origin matters when a business week starts on a chosen day or a shift starts at a particular hour. Without it, a technically valid bucket can be misaligned with the report.

I put the origin in the query or configuration rather than relying on a reader to infer it. A fifteen-minute bucket that starts at midnight is easy. A production shift that starts at 6 a.m. needs a deliberate anchor.

Compare one boundary value just before and after the expected change. Those two rows reveal more than a large chart. The older DATEADD and DATEDIFF arithmetic can remain for compatibility, but DATE_BUCKET states the intention more clearly.

SELECT DATE_BUCKET(minute, 15,
           CONVERT(datetime2(0), '2025-03-18T14:22:00'))
       AS QuarterHourStart;

Use GREATEST and LEAST Across Columns

GREATEST and LEAST compare several expressions in one row. They can replace a nest of CASE branches for latest of three timestamps or smallest of several limits. They do not aggregate across rows. Check NULL behavior: the functions ignore NULL when another argument is present.

I test equal values, one NULL, all NULL, and mixed types before replacing an old expression. A CASE statement can carry a different NULL rule. A shorter replacement should preserve the business result, not simply compile.

Explicit casts help when argument types differ. SQL Server applies type precedence, and that can change the result type or fail conversion. The helper is small. The type contract still deserves attention.

SELECT GREATEST(CONVERT(date, '2025-01-03'),
                CONVERT(date, '2025-01-05')) AS LatestDate,
       LEAST(12, 18, 9) AS SmallestLimit;

Keep Token Positions With STRING_SPLIT

A delimited input can be split into rows with STRING_SPLIT. The ordinal option on supported versions returns each token’s one-based position. This replaces attempts to infer order from a result set that has no guaranteed order. Sort by ordinal in the outer query.

If order is irrelevant, the basic split is enough. If the string represents a sequence, the position belongs in the data contract. Do not attach ROW_NUMBER to unordered split rows and call it original position.

I test repeated values and empty tokens. They expose weak parsing assumptions quickly. A normalized child table is still a better long-term design for a recurring many-value attribute. The helper is useful at an input boundary.

SELECT value, ordinal
FROM STRING_SPLIT(N'open,review,open', N',', 1)
ORDER BY ordinal;

Use JSON T-SQL Features With a Clear Schema

OPENJSON can parse a JSON array into rows with an explicit WITH clause. ISJSON checks whether text is valid JSON. Newer SQL Server environments also have a native json type, but support and syntax should be checked against the target instance before changing storage.

I prefer extracting stable business fields into typed columns for frequent filters and joins. A JSON document can retain flexible source detail, while relational columns support constraints and indexes. The presence of a JSON function does not make every table a document store.

Test missing properties, invalid types, and duplicate keys in a sample payload. A parser can return NULL values that need validation. Record rejected source bodies under the appropriate privacy controls.

DECLARE @payload nvarchar(max) =
    N'{"items":[{"id":1,"amount":12.50}]}';
SELECT SourceId, Amount
FROM OPENJSON(@payload, '$.items')
WITH (SourceId int '$.id', Amount decimal(18,2) '$.amount');

Adopt New T-SQL Features One at a Time

For each newer function, keep a before-and-after result test. Check type, NULL handling, boundary behavior, and performance where the expression appears in a large query. A function that makes SELECT clearer can still make an indexed WHERE predicate harder to seek.

I use newer syntax when it removes repeated work or makes intent plain. I leave a stable older expression alone when the change adds no value and the deployment surface is broad. The team should understand both versions during a transition.

Recent T-SQL features are tools, not a scorecard. The best improvement is a query whose result and assumptions are easier to explain. Choose that improvement, test it on the actual server, and then add it to the team’s normal style.

Which feature solves a problem you have today, rather than simply making an old query shorter? Start with a supported compatibility level and a regression test. A function can be available in an engine version yet behave differently under an older database compatibility level. Check both before using it in shared code. I document the lowest supported server for each script so a reader knows where it can run.

When modernizing a query, compare NULL handling, return type, and ordering with the original form. A short expression can change implicit conversion or collation rules. Test boundary values, not only ordinary rows. Keep the old implementation until the new one has been checked against the production data shape and execution plan.

Related reading on this blog: SQL SERVER 2022: GENERATE_SERIES Function and A Walkthrough: DATETRUNC Function in SQL Server.

A newer T-SQL feature is not an upgrade by itself, it is a clearer tool for a tested rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.