QUALIFY, GROUP BY ALL and MEDIAN in Fabric Warehouse: what the new T-SQL preview means for your queries

Fabric Data Warehouse just got some of the most practical T-SQL additions in years. Microsoft announced QUALIFY, GROUP BY ALL, ORDER BY ALL and a FROM ... SELECT query format in preview on the Fabric Updates Blog, together with new analytical functions such as MEDIAN and QUANTILE. They apply to both the warehouse and the SQL analytics endpoint of your Fabric items.

None of this changes what you can calculate. It changes how much SQL you have to write and read to get there. That matters in a warehouse, where most of the work is views, CTAS statements and the same top-N logic written for the hundredth time.

QUALIFY: filter on window functions without a CTE

The classic pattern is "latest row per customer" or "top product per category". In standard T-SQL you compute ROW_NUMBER() in a CTE or subquery and then filter on it in an outer query. With QUALIFY you filter directly:

SELECT ProductSubcategoryID, ProductID, [Name], ListPrice
FROM Production.Product
WHERE ProductSubcategoryID IS NOT NULL
QUALIFY 1 = ROW_NUMBER() OVER (
    PARTITION BY ProductSubcategoryID
    ORDER BY ListPrice DESC);

That example comes straight from the Microsoft Learn page for the clause. QUALIFY runs after WHERE, GROUP BY, HAVING and the window functions, but before ORDER BY. You can reference an alias from the select list or write the window function inside QUALIFY itself.

Three details worth knowing:

• It works in views, CTAS, INSERT ... SELECT, CTEs, subqueries and as the source query of a MERGE. It does not work in UPDATE or DELETE.
• The predicate must contain a window function. A plain predicate is an error that tells you to use WHERE.
• A group aggregate such as SUM() needs an OVER() clause inside QUALIFY. For filtering groups, HAVING is still the right tool.

GROUP BY ALL and ORDER BY ALL

GROUP BY ALL without a column list groups by every non-aggregate expression in the select list. ORDER BY ALL sorts by the columns in the select list. Fewer lines, fewer places to forget a column when someone adds one later.

Be careful here. SQL Server has an old, deprecated GROUP BY ALL with a different meaning, and that is not what Fabric implements. The Fabric version is documented as the form without a column list. If you move code between SQL Server and Fabric, read it rather than trusting the keywords.

I would also use it sparingly in shared production code. An explicit GROUP BY documents intent, and in a long query that is worth a few extra words. It is great for exploration and short views.

MEDIAN, QUANTILE and the approximate versions

The new functions are MEDIAN, QUANTILE, APPROX_MEDIAN and APPROX_QUANTILE. Each works as an aggregate and as a window function with PARTITION BY.

• MEDIAN and QUANTILE are exact. Learn documents MEDIAN as equivalent to PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...), so the result is interpolated and may not exist in your data.
• The APPROX_ versions are meant to reduce latency and memory on large datasets, and their results can vary slightly between executions.
• DISTINCT is not supported, and neither are character, date, time or datetime expressions.

Median order value, p90 claim amount or p95 latency per service are the obvious uses. These metrics were always possible, just clumsy.

What this means for you

1. Rewrite one ugly view first. Find a deduplication or top-N view with a ranking CTE, rewrite it with QUALIFY, and compare readability and run time. Do not rewrite everything.
2. Watch portability. QUALIFY, MEDIAN, QUANTILE and APPROX_MEDIAN are not supported in SQL Server or Azure SQL Database according to Learn. If a query has to run on both, keep it standard.
3. Test approximate functions against exact ones. Run both on your own data and decide what error is acceptable. Keep exact results for anything financial.
4. Treat it as preview. Test in a development workspace before you build a production pattern on it, and check the Learn pages for the current status.
5. Update your team conventions. Decide when GROUP BY ALL is acceptable. Better to agree now than to argue in a code review later.

Takeaway

These are small features that remove a lot of boilerplate, which is exactly the kind of improvement I like. After two decades in data, I have learned that shorter SQL is only better when the next person can still tell what it does. Use the new syntax where it makes intent clearer, and leave explicit code alone where it already does.

Which of these would you use first, and which would you ban from your codebase?

Sources

• T-SQL language enhancements in Microsoft Fabric Data Warehouse (Preview) (Fabric Updates Blog)
• New analytical functions in Microsoft Fabric Data Warehouse (Preview) (Fabric Updates Blog)
• SELECT - QUALIFY clause (Microsoft Learn)
• MEDIAN (Microsoft Learn)
• APPROX_MEDIAN (Microsoft Learn)
• T-SQL surface area in Fabric Data Warehouse (Microsoft Learn)