Median on Big Tables: PERCENTILE_CONT vs a Two-Row Seek

Median on big tables sounds like a simple question, but the method you pick decides how much work SQL Server does. PERCENTILE_CONT is the exact answer in one line. Reading only the middle rows from an index can be gentler. First, get the answer right on a tiny table.

Write down the rules before you compute

Say your finance team asks for the median order value. Their spreadsheet shows one number, your query shows another, and now there is a meeting. Most of the time, the two sides applied different rules.

So settle three rules first. Leave NULL values out. Use a numeric type with enough range. And for an even count, average the two middle values. The demo uses four real values and one NULL, with an index on the value. It uses a temp table, so nothing stays behind.

DROP TABLE IF EXISTS #MedianValues;

CREATE TABLE #MedianValues (Id int PRIMARY KEY, Value decimal(18,2));
INSERT #MedianValues VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, NULL);
CREATE INDEX IX_MedianValues ON #MedianValues (Value, Id);

Run all the blocks in this post in the same query window, because a temp table lives only as long as your session.

Ask PERCENTILE_CONT for the exact answer

PERCENTILE_CONT(0.5) gives the continuous median. With four values it averages 20 and 30 and returns 25. The window form with OVER() repeats that answer on every row, so I add DISTINCT to get a single line.

SELECT DISTINCT
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) OVER () AS ContinuousMedian
FROM #MedianValues
WHERE Value IS NOT NULL;

It is short and correct. But on a big table, it has to sort or scan every qualifying value before it can answer. On millions of rows, look at the actual plan for a Sort operator and a memory grant.

Fetch only the middle rows

The other way is to count the qualifying rows, jump to the middle, and read one or two rows. The count is @n. With four values, the offset is 1 and we fetch 2 rows, which are 20 and 30. With an odd count, we fetch just 1 row. The index on Value lets SQL Server read them already in order.

DECLARE @n bigint = (SELECT COUNT_BIG(*) FROM #MedianValues WHERE Value IS NOT NULL);

IF @n > 0
    SELECT AVG(CAST(Value AS decimal(28, 6))) AS MiddleRowMedian
    FROM
    (
        SELECT Value
        FROM #MedianValues
        WHERE Value IS NOT NULL
        ORDER BY Value, Id
        OFFSET ((@n - 1) / 2) ROWS FETCH NEXT (2 - @n % 2) ROWS ONLY
    ) AS MiddleValues;

I call this a two-row seek in the title, but be careful with that word. OFFSET is not a jump to row number 500,000. SQL Server still walks past the earlier index entries to get there. You save the sort, not the reading. The Id in the ORDER BY breaks ties between equal values, so the result is the same every run.

Check odd, even, and empty sets

Small cases are where median code goes wrong. Delete the value 40 and three values remain, so the middle one is 20. Then empty the table. With no non-NULL rows there is no median at all, which is why the second method checks the count first.

DELETE #MedianValues WHERE Id = 4;

SELECT DISTINCT
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) OVER () AS OddMedian
FROM #MedianValues
WHERE Value IS NOT NULL;

TRUNCATE TABLE #MedianValues;

SELECT COUNT_BIG(*) AS NonNullCount
FROM #MedianValues
WHERE Value IS NOT NULL;

The screenshot gathers the four results from the last three blocks. Both methods give 25 for the even set. The odd set gives 20, and the empty set has a count of zero.

Measure on your own big table

Four rows prove the logic and nothing about speed. To compare the two methods, use a copy of your real table. Run SET STATISTICS IO ON and turn on the actual execution plan. Run both queries and compare the logical reads and the plan shape. Then check that both return the same value.

Do not trust a blog post for a winner, including this one. The best method depends on your index, your row count, and how often you ask. Write down what you measured, so the next person does not have to guess. The last block removes the temp table.

DROP TABLE IF EXISTS #MedianValues;

Get the small cases right first, then let the big table settle the speed question.

A median is not a speed trick, it is the middle of a clearly defined ordered set.

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