Three T-SQL Tricks You May Not Be Using Yet

Three T-SQL features that have shipped over the last few releases and quietly retired patterns many of us are still using out of habit. Each replaces a stale workaround with one line of code, and in two of three cases it runs much faster, too. Take a look, try them out.

1. APPROX_COUNT_DISTINCT -- when COUNT(DISTINCT) is too expensive

Available since SQL Server 2019. COUNT(DISTINCT col) on a high-cardinality column requires SQL Server to track every unique value it has seen, which means a memory grant proportional to cardinality and a hash aggregate that loves to spill to tempdb on big tables. APPROX_COUNT_DISTINCT uses HyperLogLog instead, which trades a small accuracy hit for dramatically lower memory and faster runs.

Haven't heard of HyperLogLog before? Short story: HyperLogLog (HLL) in SQL Server is used to provide extremely fast, approximate counts of unique values (cardinality) in massive datasets using very little memory. Implemented with the APPROX_COUNT_DISTINCT function, allowing analytics on billions of rows with a roughly 2% error margin, significantly faster than COUNT(DISTINCT). Pretty cool. I use this a lot in trading data.

Build a five-million-row table with a few hundred thousand distinct customers (takes a few seconds on a laptop):

WITH N AS
(
    SELECT TOP (5000000)
        OrderID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
    CROSS JOIN sys.all_objects c
)
SELECT
    OrderID,
    CustomerID = ABS(CHECKSUM(NEWID())) % 250000,
    OrderDate = DATEADD(SECOND, OrderID, '2020-01-01'),
    Amount = CAST(OrderID % 1000 AS DECIMAL(10,2))
INTO dbo.OrdersDemo
FROM N;

CREATE CLUSTERED INDEX IX_OrdersDemo_OrderID ON dbo.OrdersDemo (OrderID);

The old way:

SET STATISTICS TIME, IO ON;

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM dbo.OrdersDemo;

The new way:

SELECT APPROX_COUNT_DISTINCT(CustomerID) AS UniqueCustomers
FROM dbo.OrdersDemo;

SET STATISTICS TIME, IO OFF;

Run both, compare CPU time and elapsed time, or even pull the actual execution plan to compare the memory grant on the root operator. You can clearly see, APPROX_COUNT_DISTINCT finishes noticeably faster with a smaller grant.

And the memory grant...

Don't use it for anything that has to be exact -- billing, regulatory counts, reconciliation. Do use it for the dozens of dashboards and exploratory queries where 'about a million' is the same answer as 'exactly 1,003,847'.

2. STRING_AGG -- retire the FOR XML PATH('') hack

Available since SQL Server 2017. Many shops still use legacy code building delimited strings with FOR XML PATH('') and STUFF(). It worked, technically. It was also suboptimal performance.

Setup -- a tiny order schema with a few items per order:

CREATE TABLE dbo.Products
(
    ProductID    INT PRIMARY KEY,
    ProductName  NVARCHAR(50)
);

CREATE TABLE dbo.Orders
(
    OrderID      INT PRIMARY KEY,
    CustomerID   INT
);

CREATE TABLE dbo.OrderItems
(
    OrderID    INT,
    ProductID  INT
);

INSERT dbo.Products VALUES
(1, 'Coffee'), (2, 'Tea'), (3, 'Cookies'), (4, 'Cake'), (5, 'Sandwich');

INSERT dbo.Orders VALUES
(1001, 50), (1002, 50), (1003, 51), (1004, 52);

INSERT dbo.OrderItems VALUES
(1001, 1), (1001, 3),
(1002, 2), (1002, 4), (1002, 5),
(1003, 1), (1003, 2),
(1004, 5);

The old way:

SELECT
    o.OrderID,
    o.CustomerID,
    Products = STUFF((
        SELECT ', ' + p.ProductName
        FROM dbo.OrderItems oi JOIN dbo.Products p 
          ON oi.ProductID = p.ProductID
        WHERE  oi.OrderID = o.OrderID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM dbo.Orders o
ORDER BY o.OrderID;

The new way:

SELECT
    o.OrderID,
    o.CustomerID,
    Products = STRING_AGG(p.ProductName, ', ')
                 WITHIN GROUP (ORDER BY p.ProductName)
FROM dbo.Orders o JOIN dbo.OrderItems oi 
  ON o.OrderID = oi.OrderID JOIN dbo.Products p
    ON oi.ProductID = p.ProductID
GROUP BY o.OrderID, o.CustomerID
ORDER BY o.OrderID;

Both produce the same resultset:

But look at that implicit conversion, larger plan cache and higher subtree cost in one vs the other:

3. GENERATE_SERIES -- kill the homemade tally CTE

This one was introduced in SQL Server 2022. Every DBA has copy-pasted some variant of a recursive CTE to generate a sequence of numbers or dates. The classic use case: a date-spine report that fills in zeros for days with no activity. GENERATE_SERIES replaces it with one function call.

Setup -- a sales table with deliberately sparse dates:

CREATE TABLE dbo.DailySales
(
    SaleDate DATE,
    Amount   DECIMAL(10,2)
);

INSERT dbo.DailySales VALUES
('2026-01-02', 1200.00),
('2026-01-03',  450.00),
('2026-01-05', 2300.00),
('2026-01-08',  890.00),
('2026-01-10', 1100.00);

The old way -- recursive CTE for the date spine:

DECLARE @StartDate DATE = '2026-01-01';
;WITH Days AS
(
    SELECT @StartDate AS TheDate, 0 AS n
    UNION ALL
    SELECT DATEADD(DAY, n + 1, @StartDate), n + 1
    FROM Days
    WHERE n < 9
)
SELECT
    d.TheDate,
    Amount = ISNULL(s.Amount, 0)
FROM Days d LEFT JOIN dbo.DailySales s 
  ON s.SaleDate = d.TheDate
ORDER BY d.TheDate
OPTION (MAXRECURSION 0);

The new way:

DECLARE @StartDate DATE = '2026-01-01';
SELECT
    TheDate = DATEADD(DAY, gs.value, @StartDate),
    Amount = ISNULL(s.Amount, 0)
FROM GENERATE_SERIES(0, 9) gs LEFT JOIN dbo.DailySales s
  ON s.SaleDate = DATEADD(DAY, gs.value, @StartDate)
ORDER BY TheDate;

Both produce the same ten-row date spine with zeros filled in for the empty days, but compare the two constructs. With GENERATE_SERIES we've got no CTE, no MAXRECURSION and no helper table. GENERATE_SERIES accepts (start, stop) or (start, stop, step), supports negative steps, and returns a single column called value. The Optimizer treats it as a normal table-valued function and can parallelize work that depends on it -- something the recursive CTE cannot do.

Why we are still writing the old versions

Full disclosure: this post happened because I caught myself reaching for a recursive CTE last night before remembering the GENERATE_SERIES improvement. Habits stick. But they can be broken easily.

The recursive tally CTE was the first 'clever' T-SQL many of us wrote. The FOR XML PATH trick was THE Stack Overflow answer for a decade. COUNT(DISTINCT) still gets typed reflexively when 'roughly how many' would do.

None of these replacements are new. STRING_AGG turned eight this year. APPROX_COUNT_DISTINCT shipped in 2019, and GENERATE_SERIES has been around since 2022. Time to check them out.

More to Read

Microsoft Learn: APPROX_COUNT_DISTINCT
Microsoft Learn: STRING_AGG
Microsoft Learn: GENERATE_SERIES