17.05 - TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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;