Top

Introduction

The Top operator returns only the first rows from its input, unmodified. Depending on its properties, Top returns either up to a specified number of rows, optionally skipping some rows first, or up to a specified percentage of the total cardinality of the input. The operator can also be configured to return extra rows based on a tie in specified columns.

The Top operator does not request more rows than it needs. For a Top operator with the Is Percent property set to false, this introduces a row goal in the optimizer logic; this can affect the plan shape of Top’s input subtree.

A Top operator with its Is Percent property set to true always has a blocking operator on its input that processes and stores the entire result set (e.g. a Sort or an eager Table Spool). Top gets a total row count from that blocking operator, which it then uses to compute the number of rows to return. In this case the row goal only applies to the operators in between the Top and the blocking operator; the input subtree of the blocking operator still produces and returns the entire input.

On segmented input, the Top operator returns the first specified number of each rows from each segment. In this case, the entire input is always consumed and no row goal is set.

The most common use cases for a Top operator are queries that use the T-SQL TOP clause; the Is Percent and With Ties properties correspond to the PERCENT and WITH TIES arguments of that clause. You may also find a Top operator if a SET ROWCOUNT is in effect, or as a result of optimizations by the query optimizer. Finally, sometimes a “useless” Top operator is left in an execution plan that was added during intermediate phases of optimization and then not removed due to its low cost.

Visual appearance in execution plans

Depending on the tool being used, a Top operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Top operator is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called. It also does not show the optimization that prevents reading an extra row if the With Ties property is false.

Is percent?

If the Is Percent property is true, the Top Expression is not an integer value denoting the number of rows to return, but a floating point value representing the number of rows to return as a percentage of the total number of rows in the input subtree. An execution plan that includes a Top operator with Is Percent is true always has a blocking operator (usually a Sort or an eager Table Spool) on the input subtree of that Top, often as its immediate child. Though not directly visible in the execution plan, the Top operator can poll this blocking operator to request the total number of rows it has processed from its input (“Get child cardinality”). The actual number of rows to be returned (“Compute Top rows”) is then determined as Top Expression percent of that total number of rows, rounded up to the next integer.

OffsetExpression?

If the optional OffsetExpression property is present, it is an integer specifying the number of rows to skip before returning any rows. This is done by either counting number of rows skipped and comparing it to OffsetExpression, or by decreasing OffsetExpression for each row skipped until it reaches zero.

Top rows returned?

The operator tracks number of rows returned so it can stop returning when sufficient rows were returned. This is done by either counting the number of rows returned and comparing it to the number of rows to return (either Top Expression, or the number computed from Top Expression and input cardinality when Is Percent is true), or by decreasing the number of rows to return for each row returned until it reaches zero.

Tied?

After the operator has skipped and returned the number of rows as specified by OffsetExpression, Top Expression, and Is Percent, it can optionally return more rows if the With Ties property is set to true and the values of all Tie Columns in the next input rows are all equal to the values in the last row returned. This repeats as often as needed, until a value changes or the end of the input data is reached.

The flow chart above suggests that the operator will always read an extra row after returning the “last” row, before verifying the “Tied?” condition. In reality, there is some extra logic to bypass the superfluous “Read next row” call when the With Ties property is false, because in that case the row would never be returned anyway. For readability reasons I have not included this extra logic in the flow chart.

Segment-aware operation

If a Top operator has Segment as its immediate child operator, its operation changes. As far as known, a segmented Top operation always has the Is Percent and Is Row Count properties set to false, and does not have an OffsetExpression property.

For the segmented version of the Top operator, the algorithm is as follows:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.

A segmented Top always consumes the entire input. It resets its counters on each segment change, so that instead of returning only the first specified number of rows from the entire input, it returns the first specified number of rows from each individual segment. It can also include tied rows from each segment individually.

Operator properties

The properties below are specific to the Top operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Index Seek operator are marked with a *.

Property name	Description
Is Percent	When this is false, the value in the Top Expression property indicates the number of rows to return; when true the Top Expression is given as a percentage of the total cardinality of the input instead. See main text for more details.
Is Row Count	This property is set to true if the TOP operator is in the plan as the result of prior execution of a SET ROWCOUNT statement, rather than a TOP or FETCH expression in the query.
OffsetExpression	When present, this indicates the number of rows to skip before starting to return rows.
Tie Columns	Lists the columns that are checked for change to determine whether rows from the input that follow the last row to return are returned as well. Only present when With Ties is true.
Top Expression	This property is an integer number to represent the number of rows to return if Is Percent is false; or a floating point number to represent the percentage of rows to return if Is Percent is true.
With Ties	When this is true, rows from the input that follow the last row to return but have the same values in all columns indicated by Tie Columns are returned as well.

Implicit properties

This table below lists the behavior of the implicit properties for the Top operator.

Property name	Description
Batch Mode enabled	The Top operator supports row mode execution only.
Blocking	The Top operator is non-blocking. When the Is Percent property is true, the Top operator does require a blocking operator on its input.
Memory requirement	The Top operator does not have any special memory requirement.
Order-preserving	The Top operator is fully order-preserving.
Parallelism aware	The Top operator is not parallelism aware.
Segment aware	The Top operator is segment aware. This is described in more detail in the main text.