Using the TOP operator in T-SQL procedures is a common practice among developers. It’s often employed to limit the number of records, either to fetch the first value or to ensure that only a single value is assigned when working with variables. Consider the following example:
DECLARE @var INT = (SELECT TOP 1 purpose_name FROM purpose_name)
SELECT @var
Despite its simplicity, developers might overlook how the TOP operator can impact performance. The optimizer employs different heuristics when the TOP operator is in play, affecting its job of limiting rows similar to a WHERE condition. While it’s commonly used to limit rows for output, it can also be used within a subquery or correlated query, as demonstrated below:
-- TOP operator within a join
SELECT ...
FROM table1
JOIN (SELECT TOP 1 column FROM table2) AS subquery ON ...
Notice the TOP operator is in the middle of the execution plan , before the join operator
Now, let’s explore how a query behaves differently with and without the TOP operator. In the example below, the impact on optimization for parameter sniffing is evident due to the absence of parameterization caused by the TOP operator:
-- Query with TOP operator
SELECT ...
WHERE column = 1241348
Compare this to the same query without the TOP operator:
-- Query without TOP operator
SELECT ...
WHERE column = @parameter
You might wonder if you should avoid using the TOP operator because it affects parameterization. The answer is no. Consider the following query that could potentially return 1000 rows without the TOP operator:
Without Top | With Top | |
 |  | |
Table ‘acts_sections_new’. Scan count 1, logical reads 4488, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
| Table ‘acts_sections_new’. Scan count 1, logical reads 49, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. | |
The difference in execution plans and IO stats between the query with and without the TOP operator demonstrates its role in reducing unnecessary scans:
- Without TOP Operator:
- Scan count 1, logical reads 4488
- With TOP Operator:
- Scan count 1, logical reads 49
Clearly, the TOP operator prevents extensive scans as soon as the specified limit is reached, offsetting any performance loss due to the lack of parameterization. However, it’s worth noting that the TOP operator doesn’t provide significant benefits when the query is expected to return only one row and the operator is set to TOP 1.
Without Top | With Top |
 |  |
Table ‘acts_sections_new’. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. | Table ‘acts_sections_new’. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. |
In summary, use the TOP operator judiciously. It plays a crucial role in optimizing queries by limiting the result set, but its impact on performance should be considered based on the specific use case and expected outcomes.