Asking for five rows does not guarantee that SQL Server reads only five candidates. A TOP N Sort must find the best rows before it knows which ones to return. An appropriate ordered index can change that work, especially when its key order also matches the filter.
Separate Returned Rows From Examined Rows
TOP limits the rows returned by the statement. It does not automatically limit the input SQL Server must inspect. Without a suitable access path for ORDER BY, the engine has to determine which rows belong at the top of the requested order.
I check the input rows and scan work before treating a small result as a cheap query. A Top N Sort keeps only a bounded set of leading candidates while it processes the input. It still examines every eligible row when no ordered path can establish the winners earlier.
The demonstration uses a dedicated table with synthetic scores and categories. ID resolves ties in Score, making the chosen rows deterministic. Decide that tie rule before tuning. If the business wants all tied scores, TOP WITH TIES has a different result contract. Asking for only five rows is polite. The table does not know which five you mean until ordering is resolved.
Capture the Unindexed TOP N Sort Plan
The clustered primary key orders the sample by ID, not by Score descending. Enable actual execution plans and run the first TOP query. Inspect the Sort operator's properties for its Top N behavior, then read STATISTICS IO for the table access.
The generated population is a deliberate test input. No observed read count or duration is claimed here. Use representative data for a real tuning decision and retain the table definition, query, and actual plan with your own measurements.
I keep the projected columns stable between comparisons. Removing a required display column can eliminate lookup work and make an alternative seem better for the wrong reason. The baseline returns ID, Score, CategoryID, and TitleText. The proposed index must either cover those columns or account for retrieving them. Correct row identity and display content belong in the test alongside the performance evidence.
CREATE TABLE dbo.TopSortDemo
(ID int NOT NULL PRIMARY KEY,Score int NOT NULL,CategoryID int NOT NULL,TitleText varchar(40));
WITH Numbers AS
(
SELECT TOP(100000) ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.TopSortDemo
SELECT CONVERT(int,n),CONVERT(int,n%1000),CONVERT(int,n%10),'Sample' FROM Numbers;
SET STATISTICS IO ON;
SELECT TOP(5) ID,Score,CategoryID,TitleText
FROM dbo.TopSortDemo ORDER BY Score DESC,ID;
SET STATISTICS IO OFF;Remove the TOP N Sort With an Ordered Index
The next index begins with Score descending and ID ascending, matching the requested ordering. Included columns cover the rest of the projection. Inspect whether the actual plan uses ordered index access followed by Top, without the original sort. In my test, the first query scanned the whole table into a TopN Sort, and this indexed version read only the rows it returned.
An ordered access path can stop after enough rows satisfy the request. It does not require a seek when there is no search predicate; an ordered scan can be exactly the useful plan. Read the Ordered property and the actual rows processed rather than rejecting the word scan automatically.
What work did the index remove? Compare sorting, rows read, and any lookups through actual evidence. The optimizer can still choose another path under different conditions. An index definition creates an opportunity, not a command that every request must use it. Keep the output identical and compare representative executions before accepting the new storage and maintenance cost.
CREATE INDEX IX_TopSort_Score
ON dbo.TopSortDemo(Score DESC,ID) INCLUDE(CategoryID,TitleText);
SET STATISTICS IO ON;
SELECT TOP(5) ID,Score,CategoryID,TitleText
FROM dbo.TopSortDemo ORDER BY Score DESC,ID;
SET STATISTICS IO OFF;
Put an Equality Filter Before the Sort Keys
Adding WHERE CategoryID equal to a value changes the useful index order. An index on Score can still walk score order and discard unrelated categories, but it can examine many entries before finding enough matches. The category distribution determines that work.
An index beginning with CategoryID and then the sort keys can locate the category range and read its scores in the desired order. The next block creates that alternative and runs a filtered request. Keep the parameter type compatible with the column and inspect the Seek Predicates and ordering properties.
The equality condition on the leading key matters. A range across many categories does not provide one global Score ordering through the same composite index without additional work. Key order must match the actual filter and ordering relationship. Do not turn this one equality example into a universal rule that every filtered TOP query needs the same index pattern.
CREATE INDEX IX_TopSort_CategoryScore
ON dbo.TopSortDemo(CategoryID,Score DESC,ID) INCLUDE(TitleText);
DECLARE @Category int=3;
SET STATISTICS IO ON;
SELECT TOP(5) ID,Score,CategoryID,TitleText
FROM dbo.TopSortDemo WHERE CategoryID=@Category
ORDER BY Score DESC,ID;
SET STATISTICS IO OFF;Keep Tie and Pagination Rules Deterministic
A stable secondary ordering key prevents tied scores from returning arbitrary identities. The index should support that complete order when it serves a recurring request. A display order using only Score leaves equal-score ordering unspecified, even if one test appears consistent.
Pagination introduces another contract. OFFSET can require processing preceding rows, while a keyset continuation can use the complete ordered key to resume from a known point. Those are separate design choices from the small TOP demonstration and need their own correctness tests.
Check NULL sort values, changing scores, and category skew when the real schema permits them. Concurrent updates can change the candidate set between requests. Decide whether the application needs a stable snapshot or simply the current top rows. An index helps access the order efficiently, but it does not guarantee that a changing dataset presents the same winners across independent executions.
Build for Frequent Sorts and Verify the Tradeoff
An index for every possible sort column creates storage and write-maintenance costs. Identify the sorts and filter combinations that matter to the workload. A less common report can reasonably keep its sort rather than receiving another permanent index.
Compare the complete workload impact, including insert and update behavior. Keep both sample indexes only for the test; the production decision can choose one, another combined design, or neither based on evidence. Clean up the dedicated table after the experiment.
TOP N Sort explains why a small returned set can still require broad input work. Provide an appropriate ordered path when the frequent query justifies it, align equality filters with sort keys, and inspect the actual plan. The useful improvement reduces examined work while preserving the exact rows the request was meant to return.
Related reading on this blog: Top 1 and Index Scan and Performance and TempDB Spills: SQL in Sixty Seconds 208.
A small TOP is not a small input, it is a result limit that still needs the right ordering path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
