TOP n Operator Outperforms QUALIFY RANK and QUALIFY ROW_NUMBER - Teradata Vantage

The QUALIFY clause with the RANK or ROW_NUMBER ordered analytical functions returns the same results as the TOP n operator.

For best performance, use the TOP option instead of the QUALIFY clause with RANK or ROW_NUMBER. In best case scenarios, the TOP n operator provides better performance; in worst case scenarios, the TOP n operator provides equivalent performance.

For example, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY ROW_NUMBER.

     SELECT TOP 10 *
     FROM sales
     ORDER BY county;

     SELECT *
     FROM sales
     QUALIFY ROW_NUMBER() OVER (ORDER BY COUNTY) <= 10;

Similarly, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY RANK.

     SELECT TOP 10 WITH TIES *
     FROM sales ORDER BY county;

     SELECT *
     FROM sales
     QUALIFY RANK() OVER (ORDER BY county) <= 10;