Top vs Rank/Row Number functions - Which performs higher?

I attempted to Google the Cost of using Top in a query vs using a Ranking or Row_Number type function.

Does the cost of each depend on the situation or can the cost of these two features be determined across the board for all situations?

Some mock SQL is below using a simple CTE to demonstrate my question would look like the below:

WITH fData AS
(
    SELECT 1 AS ID, 'John' AS fName, 'Black' AS lName, CAST('05/19/1975' AS DATE) AS birthDate UNION ALL
    SELECT 2 AS ID, 'John' AS fName, 'Black' AS lName, CAST('04/1/1989' AS DATE) AS birthDate UNION ALL
    SELECT 3 AS ID, 'John' AS fName, 'Black' AS lName, CAST('11/16/1995' AS DATE) AS birthDate UNION ALL
    SELECT 4 AS ID, 'John' AS fName, 'Black' AS lName, CAST('01/16/1968' AS DATE) AS birthDate UNION ALL
    SELECT 5 AS ID, 'John' AS fName, 'Black' AS lName, CAST('01/16/1968' AS DATE) AS birthDate
)   

/* Using TOP 1 vs Row_Number() - Uncomment this and comment the below to VIEW TOP version */
--SELECT TOP 1 d.ID, d.fName, d.lName, d.birthDate
--FROM fData d
--ORDER BY d.birthDate

/* Using the below vs TOP 1 */
SELECT * FROM
(   SELECT d.ID, d.fName, d.lName, d.birthDate, Row_Number() OVER (ORDER BY d.birthDate) AS ranker
    FROM fData d
) r
WHERE r.ranker = 1

When using TOP there's not a need to apply a secondary Wrapping query around it and it looks cleaner. After applying a Row_Number or a Ranking function you then must wrap it to tell the query which row you are now wanting... either by applying the WHERE ranker = 1 or ranker >= 5 to achieve the same as TOP 1 or TOP 5.

Which is better faster if this is even something that can be determined?

Solution 1:

In the case of your example the TOP is somewhat more efficient.

The execution plan for TOP is below

The TOP N sort with N=1 just needs to keep track of the row with the lowest birthDate that it sees.

For the row_number query it recognises that the row number is always ascending and does itself add a TOP 1 to the plan but it doesn't combine the separated TOP and SORT into a TOP N Sort - so it does a full sort of all 5 rows.

In the case that an index supplies rows in the desired order without the need for a sort there won't be much in it. The row_number query will have an extra couple of operators that are fairly inexpensive anyway.

WHY use ranking functions in SQL Server when it has TOP

Ranking functions in general are more powerful than TOP.

For the cases where both would work consider that TOP is a fairly ancient proprietary syntax and not standard SQL. It was in the product a long time before window functions were added. If portable SQL is a concern you should not use TOP.

Though you might not use ranking functions either. As another (standard SQL) alternative is

SELECT d.ID, d.fName, d.lName, d.birthDate
FROM fData d
ORDER BY d.birthDate
OFFSET 0 ROWS
FETCH NEXT 1 ROW ONLY

which gives the same plan as TOP 1